//*********************************************************************
//		Copyright (c) 2003 - 2008 Microsoft Corporation.
//      This code is provided under Microsoft Public License, which can be found at
//      http://www.microsoft.com/resources/sharedsource/licensingbasics/publiclicense.mspx 
//
// @File: Collector_SQL.cs
//
// Purpose: Collector for SQL-specific data
//
//
//
// Notes: 
//
//
//	
// History: 
//     @Version: V2.1
//
//     07/14/08 PGV 2.1.001.0 Starting V2.1 (based on 2.0.030.0 code)
//
//
// @EndHeader@
//*********************************************************************

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.Win32;
using System.Diagnostics;
using System.Management;
using System.Collections;

using H2V2;

namespace H2V2.Collector
{
    public struct SqlInstance
    {
        public Connection cn;
        public string name;
        public string inst_name;
        public int IsDefault;
        public short inst_id;
        public bool IsRunning;
    }

	/// <summary>
	/// Collects data from SQL Server instances
	/// </summary>
	public class CollectorSql : CollectorBase
	{
		static int ms_collectorID = 0;
		string m_InstanceKey;

		Connection m_target;
		SqlInstance m_instance;

		short m_MailHostDbId = 0; 
		ArrayList m_RosettaDb_array;

		// DEBUG
		bool m_debug_ns = true;

		public override string Version { get { return "2.2.001.0"; } }
		public override string Name { get { return "SQL"; } }

		public CollectorSql(string a_machine_name, string a_inst_name, RunDescriptor a_rd) 
			: base (a_machine_name, a_inst_name, a_rd)
		{
			// default collector mask is 0x7fffffff
			// 0x1 - Databases; 0x2 - sysconfig; 0x4 - trace; 0x8 - misc registry;
			// 0x10 - instance info; 0x20 - server objects; 0x100 - db objects/properties
			// 0x1000 - sysperfinfo
			m_track_id = 1;
			m_hparam = m_hparam + ", @inst_id="+a_rd.inst_id;
		}

		#region Initialization
		public override void Init (Connection a_repository) 
		{
			m_track_id = 10;
			base.Init (a_repository);

			m_track_id = 11;
			if (ms_collectorID == 0)
			{
				ms_collectorID = RegisterCollector ();
			}
			m_collectorID = ms_collectorID;
			m_track_id = 12;
		}

		public void Init (Connection a_repository, SqlInstance a_instance, RegistryKey a_key)
		{
			m_track_id = 2;
			Init (a_repository);
			m_instance = a_instance;
			m_target = a_instance.cn;
			m_hklm = a_key;

			m_InstanceKey = GetInstanceKey ();

			m_RosettaDb_array = new ArrayList (2);

			m_track_id = 20;
			StoreInstance (); 
		}

		string GetInstanceKey ()
		{
			m_track_id = 13;
			string s_key = "";

			try
			{
				s_key = GetInstanceKey (m_hklm, m_inst_name, m_target.Version.H2level);
			}
			catch (CollectorErrorException cerr)
			{
                if (cerr.m_Error == CollectorError.CannotFindRegistryKey) 
				{
                    LogMgrError (RunErrorClass.Registry, 0, m_inst_name, CollectorError.CannotFindRegistryKey, 0, "Can't find instance-specific registry key");
				}
				throw;
			}
			
			return s_key;
		}

		#endregion

        void AddInstanceParams (SqlCommand sqlcmd)
        {
            sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
            sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
        }

        void AddInstanceDbParams (SqlCommand sqlcmd, int dbId)
        {
            sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
            sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
            sqlcmd.Parameters.AddWithValue ("@dbid", dbId);
        }

        public override void Collect ()
		{
			m_track_id = 1000;
			ChangeStatus ("Running SQL collector on "+m_instance.name);

			try
			{
				if (m_instance.IsRunning)
				{
					UnitCollectInstanceInfo ();
					UnitCollectDatabase ();
					m_track_id = 1200;
					UnitCollectConfiguration ();
					m_track_id = 1300;
					UnitCollectTrace ();
					m_track_id = 1400;
					UnitCollectServerObjects (); // must be called after UnitCollectDatabase, because of CollectDbMail dependency on c_database9
					m_track_id = 1500;
					UnitCollectSysperfinfo ();
                    m_track_id = 1600;
                    UnitCollectJobInfo();
				}

				m_track_id = 1900;
				UnitCollectRegistry ();
			}
			catch (SqlException sqlex)
			{
				if (sqlex.Number != -2) throw; // -2 is Timeout
			}
		}

		#region Unit Collection functions
		void UnitCollectInstanceInfo ()
		{
			if ((m_options & 0x10) == 0) return;

			UnitOpen (5, "Server Properties");
			try
			{
				CollectInstanceInfo ();
				UnitSaveData ();
			}
			catch (Exception ex)
			{
				ChangeStatus (ex.StackTrace, StatusEventType.STACK_TRACE);
				ChangeStatus (ex.Message,StatusEventType.ERROR);
				ChangeStatus ("Unit 5 failed", StatusEventType.ERROR);
				if (GlobalEnvironment.RepositoryFailure) throw;
			}
			finally
			{
				UnitClose ();
			}
		}

		void UnitCollectServerObjects ()
		{
			if ((m_options & 0x20) == 0) return;

			UnitOpen (6, "Server Objects");
			try
			{
				CollectServerObjects ();
				HistoryServerObjects ();
				UnitSaveData ();
			}
			catch (Exception ex)
			{
				ChangeStatus (ex.StackTrace, StatusEventType.STACK_TRACE);
				ChangeStatus (ex.Message,StatusEventType.ERROR);
				ChangeStatus ("Unit 6 failed", StatusEventType.ERROR);
				if (GlobalEnvironment.RepositoryFailure) throw;
			}
			finally
			{
				UnitClose ();
			}

		}

		void UnitCollectDatabase ()
		{
			if ((m_options & 0x1) == 0) return;

			m_track_id = 1001;
			UnitOpen (1, "Databases");

			try
			{

                ChangeStatus(m_target.Version.H2level.ToString(), StatusEventType.INFO);
                
                switch (m_target.Version.H2level)
                {
                    case H2ProductLevel.Sql2000SP3:
                        CollectDatabases2000 ();
                        break;
                    case H2ProductLevel.Sql2005:
                    case H2ProductLevel.Sql2008:
                        CollectDatabases2005 ();
                        break;
                }
				
				m_track_id = 1050;
				HistoryDatabases ();
				m_track_id = 1060;
				HistoryAllocations ();
				m_track_id = 1070;
				HistoryDbObjects ();

				UnitSaveData ();
			}
			catch (SqlException sqlex)
			{
				ChangeStatus (sqlex.StackTrace, StatusEventType.STACK_TRACE);
				ChangeStatus (sqlex.Message,StatusEventType.ERROR);
				ChangeStatus ("Unit 1 failed", StatusEventType.ERROR);

				if (sqlex.Number == -2) 
				{
                    LogMgrError (RunErrorClass.SqlServer, m_CurrentUnit, m_inst_name, CollectorError.SqlTimeout, sqlex.Number, sqlex.Message); 
					throw;	// Timeout occured - connection is dead, collector can't proceed
				}
				else
				{
                    LogMgrError (RunErrorClass.SqlServer, m_CurrentUnit, m_inst_name, CollectorError.SqlUnexpectedException, sqlex.Number, sqlex.Message); 
				}
			}
			catch (Exception ex)
			{
				ChangeStatus (ex.StackTrace, StatusEventType.STACK_TRACE);
				ChangeStatus (ex.Message,StatusEventType.ERROR);
				ChangeStatus ("Unit 1 failed", StatusEventType.ERROR);
				if (GlobalEnvironment.RepositoryFailure) throw;
			}
			finally
			{
				UnitClose ();
			}
		}

		void UnitCollectConfiguration ()
		{
			if ((m_options & 0x2) == 0) return;
			UnitOpen (2, "Current Configuration");

			try
			{
				CollectSyscurconfigs (); 
				CallHistoryProc ("sph_sysconfigs");
				UnitSaveData ();
			}
			catch (Exception ex)
			{
				ChangeStatus (ex.Message,StatusEventType.ERROR);
				ChangeStatus ("Unit 2 failed", StatusEventType.ERROR);
				if (GlobalEnvironment.RepositoryFailure) throw;
			}
			finally
			{
				UnitClose ();
			}
		}

		void UnitCollectTrace ()
		{
			if ((m_options & 0x4) == 0) return;
			UnitOpen (3, "Running traces");

			try
			{
				CollectTraceInfo ();
				CallHistoryProc ("sph_trace");
				UnitSaveData ();
			}
			catch (Exception ex)
			{
				ChangeStatus (ex.Message,StatusEventType.ERROR);
				ChangeStatus ("Unit 3 failed", StatusEventType.ERROR);
				if (GlobalEnvironment.RepositoryFailure) throw;
			}
			finally
			{
				UnitClose ();
			}
		}

		void UnitCollectRegistry ()
		{
			if ((m_options & 0x8) == 0) return;
			UnitOpen (4, "Registry");

			try
			{
				CollectRegistry ();
				UnitSaveData ();
			}
			catch (Exception ex)
			{
				ChangeStatus (ex.Message,StatusEventType.ERROR);
				ChangeStatus ("Unit 4 failed", StatusEventType.ERROR);
				if (GlobalEnvironment.RepositoryFailure) throw;
			}
			finally
			{
				UnitClose ();
			}
		}

		void UnitCollectSysperfinfo ()
		{
			if ((m_options & 0x1000) == 0 || m_target.Version.H2level != H2ProductLevel.Sql2005) return;
			
			UnitOpen (7, "SysPerfInfo");

			try
			{
				c_sysperfinfo ();
				UnitSaveData ();
			}
			catch (Exception ex)
			{
				ChangeStatus (ex.Message,StatusEventType.ERROR);
				ChangeStatus ("Unit 7 failed", StatusEventType.ERROR);
				if (GlobalEnvironment.RepositoryFailure) throw;
			}
			finally
			{
				UnitClose ();
			}
		}

        void UnitCollectJobInfo()
        {
            //havent worked out what m_options is yet.
            //if ((m_options & 0x10) == 0) return;

            UnitOpen(8, "SQL Jobs");
            try
            {
                CollectSQLJobs();
                CollectSqlJobHistory();
                HistoryJobs();
                UnitSaveData();
            }
            catch (SqlException sqlex)
            {
                ChangeStatus(sqlex.StackTrace, StatusEventType.STACK_TRACE);
                ChangeStatus(sqlex.Message, StatusEventType.ERROR);
                ChangeStatus("Unit 8 failed (SQL EX)", StatusEventType.ERROR);

                if (sqlex.Number == -2)
                {
                    LogMgrError(RunErrorClass.SqlServer, m_CurrentUnit, m_inst_name, CollectorError.SqlTimeout, sqlex.Number, sqlex.Message);
                }
                else
                {
                    LogMgrError(RunErrorClass.SqlServer, m_CurrentUnit, m_inst_name, CollectorError.SqlUnexpectedException, sqlex.Number, sqlex.Message);
                }
            }
            catch (Exception ex)
            {
                ChangeStatus(ex.StackTrace, StatusEventType.STACK_TRACE);
                ChangeStatus(ex.Message, StatusEventType.ERROR);
                ChangeStatus("Unit 8 failed", StatusEventType.ERROR);
            }
            finally
            {
                UnitClose();
            }
        }

		#endregion

		#region Instance2
		bool CollectAD ()
		{
			bool res = false;

			//HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\SQLServerSCP\objectGUID
			string key = m_InstanceKey+"\\SQLServerSCP";

			try
			{
				RegistryKey rk = m_hklm.OpenSubKey (key);
				res = (rk.GetValue ("objectGUID") != null);
			}
			catch {}

			return res;
		}

		void CollectInstanceInfo ()
		{
			SqlCommand cmd;
			SqlDataReader dr;
			string s_cmd, 
				Collation,
				LicenseType;
			int NumLicenses = 0;
            bool IsClustered,
                IsFullTextInstalled,
                IsIntegratedSecurityOnly,
                IsSingleUser,
				IsAD;
            short EngineEdition = 0,
                iLicenseType = 0;


			IsAD = CollectAD ();

			s_cmd = 
@"Select  SERVERPROPERTY('Collation') [Collation], 
SERVERPROPERTY('EngineEdition') [EngineEdition], 
SERVERPROPERTY('IsClustered') [IsClustered], 
SERVERPROPERTY('IsFullTextInstalled') [IsFullTextInstalled], 
SERVERPROPERTY('IsIntegratedSecurityOnly') [IsIntegratedSecurityOnly], 
SERVERPROPERTY('IsSingleUser') [IsSingleUser], 
SERVERPROPERTY('LicenseType') [LicenseType], 
IsNULL(SERVERPROPERTY('NumLicenses'),0) [NumLicenses] ";

			cmd = new SqlCommand (s_cmd, m_target.SQLConnection);
			dr = cmd.ExecuteReader ();
			try
			{
				if (dr.Read ())
				{
					Collation = dr.GetString (0);
					EngineEdition = (short)dr.GetInt32 (1);
					IsClustered = dr.GetInt32 (2) == 1;
					IsFullTextInstalled = dr.GetInt32 (3) == 1;
					IsIntegratedSecurityOnly = dr.GetInt32 (4) == 1;
					IsSingleUser = dr.GetInt32 (5) == 1;
					LicenseType = dr.GetString (6);
					NumLicenses = dr.GetInt32 (7);

					switch (LicenseType)
					{
						case "PER_SEAT": iLicenseType = 1;
							break;
						case "PER_PROCESSOR": iLicenseType = 2;
							break;
						case "DISABLED": iLicenseType = 3;
							break;
					}

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText = 
@"Insert c_instance2 (run_id,srv_id,inst_id,Collation,EngineEdition,
IsClustered,IsFullTextInstalled,IsIntegratedSecurityOnly,IsSingleUser,LicenseType,NumLicenses,IsAD) 
Values (@run_id,@srv_id,@inst_id,@Collation,@EngineEdition,
@IsClustered,@IsFullTextInstalled,@IsIntegratedSecurityOnly,@IsSingleUser,@LicenseType,@NumLicenses,@IsAD) ";
                    AddInstanceParams (sqlcmd);
                    sqlcmd.Parameters.AddWithValue ("@Collation", Collation);
                    sqlcmd.Parameters.AddWithValue ("@EngineEdition", EngineEdition);
                    sqlcmd.Parameters.AddWithValue ("@IsClustered", IsClustered);
                    sqlcmd.Parameters.AddWithValue ("@IsFullTextInstalled", IsFullTextInstalled);
                    sqlcmd.Parameters.AddWithValue ("@IsIntegratedSecurityOnly", IsIntegratedSecurityOnly);
                    sqlcmd.Parameters.AddWithValue ("@IsSingleUser", IsSingleUser);
                    sqlcmd.Parameters.AddWithValue ("@LicenseType", iLicenseType);
                    sqlcmd.Parameters.AddWithValue ("@NumLicenses", NumLicenses);
                    sqlcmd.Parameters.AddWithValue ("@IsAD", IsAD);
                    
					UnitAddData (sqlcmd);
				}
			}
			finally 
			{
				dr.Close ();
			}
		}

		#endregion

		void CollectSyscurconfigs () 
		{
			SqlDataAdapter rda, 
				tda;
			DataTable rdt,
				tdt;
			DataRow [] rrows;
			short tconfig;
			int rvalue, tvalue,level;

			level = (int)m_target.Version.H2level;

            SqlCommand sqlcmd = new SqlCommand (
                "Select value, config From dbo.d_syscurconfigs Where ver_id=@level Order by config", m_repository.SQLConnection);
            sqlcmd.Parameters.AddWithValue ("@level", level);

            rda = new SqlDataAdapter (sqlcmd);
			tda = new SqlDataAdapter ("Select value, config From master.dbo.syscurconfigs Order by config", m_target.SQLConnection);

			rdt = new DataTable ();
			tdt = new DataTable ();
			
			rda.Fill (rdt);
			tda.Fill (tdt);

			foreach (DataRow row in tdt.Rows)
			{
				tconfig = (short)row["config"];
				tvalue = (int)row["value"];

				rrows = rdt.Select ("config = "+tconfig);
				if (rrows.Length == 0)
				{
					StoreSyscurconfigs (tvalue, tconfig);
					ChangeStatus ("Unknown syscurconfigs entry: "+tconfig+", "+tvalue, StatusEventType.DEBUG);
				}
				else
				{
					rvalue = (int)rrows[0]["value"];
					if (tvalue != rvalue) 
					{
						StoreSyscurconfigs (tvalue, tconfig);
					}
				}
			}
		}

		
		#region Sql2000 databases
		void CollectDatabases2000 ()
		{
			SqlDataAdapter db_da;
			DataTable db_tab;
            bool IsSystem,
				oAutoClose,
				oAutoShrink,
				oAutoCreateStat,
				oAutoUpdateStat,
				oOffline,
				oReadOnly,
				oSelectIntoBulkCopy,
				oPublished,
				oSubscribed,
				oMergePublished,
				oMergeSubscribed,
				oFullTextEnabled,
				oDbChaining,
				oTornPageDetection,
				oTruncateLogOnCheckpoint;

			int dbId,
				compat,
				oRecoveryModel,
				oUserAccess = 0;
			long spaceusedReserved,
				spaceusedUsed,
				spaceusedText,
				spaceusedData,
				dbDataSpaceUsage = 0,
				dbIndexSpaceUsage = 0;
			uint status,
				status2,
				category;
			string cmd,
				dbName,
                dbQName,
				recovery;
			DateTime crdate,
				lastBackup,
                lastDiffBackup,
                lastLogBackup;
			Decimal dbSize = 0,
				txSize = 0,
                txSpaceAvailable = 0,
				unallocated = 0,
				dbSpaceAvailable = 0;

			m_track_id = 1010;
            ChangeStatus(m_target.SQLConnection.ConnectionString, StatusEventType.INFO);
			cmd = "Select dbid, name, QUOTENAME(name) As [qname], status, status2, crdate, category, cmptlevel, DATABASEPROPERTYEX(name,'Recovery') As [Rec] From master.dbo.sysdatabases";
            ChangeStatus(cmd, StatusEventType.INFO);
            db_da = new SqlDataAdapter (cmd, m_target.SQLConnection);
			db_tab = new DataTable ();

			db_da.Fill (db_tab);

			m_track_id = 1011;
			foreach (DataRow row in db_tab.Rows)
			{
                ChangeStatus("Getting info for db " + (string)row["name"], StatusEventType.INFO);
				dbId = (int)(short)row["dbid"];
				dbName = (string)row["name"];
                dbQName = (string)row["qname"];
				m_track_base = 10000 * dbId;
				m_track_id = m_track_base + 1;

				try
				{
					m_target.SQLConnection.ChangeDatabase (dbName);
				}
				catch (SqlException sqlex)
				{
					if (sqlex.Number == -2) 
					{
                        LogMgrError (RunErrorClass.SqlServer, m_CurrentUnit, m_inst_name, CollectorError.SqlTimeout, sqlex.Number, sqlex.Message); 
						throw;	// Timeout occured - connection is dead, collector can't proceed
					}
					else
					{
                        LogMgrError (RunErrorClass.Database, dbId, dbName, CollectorError.SqlDbInaccessible, sqlex.Number, sqlex.Message);
						ChangeStatus (sqlex.Message, StatusEventType.ERROR);
						ChangeStatus ("Database-specific information will not be collected", StatusEventType.WARNING);
						continue;
					}
				}

				m_track_id = m_track_base + 2;
				status = (uint)(int)row["status"];
				status2 = (uint)(int)row["status2"];
				crdate = (DateTime)row["crdate"];
				category = (uint)(int)row["category"];
				compat = (int)(byte)row["cmptlevel"];
				recovery = (string)row["Rec"];

				oAutoClose = (status & 1) > 0;
				oAutoShrink = (status & 4194304) > 0;
				oAutoCreateStat = (status2 & 16777216) > 0;
				oAutoUpdateStat = (status2 & 1073741824) > 0;
				oOffline = (status & 512) > 0;
				oReadOnly = (status & 1024) > 0;
				oSelectIntoBulkCopy = (status & 4) > 0;
				oTornPageDetection = (status & 16) > 0; 
				oTruncateLogOnCheckpoint = (status & 8) > 0;
				oFullTextEnabled = (status2 & 536870912) > 0;
				oDbChaining = (status2 & 1024) > 0; 

				// supposedly these 2 options cannot be set both at the same time
				oUserAccess = (status & 2048) > 0 ? 2 : 0; // DBOOnly
				oUserAccess = (status & 4096) > 0 ? 1 : 0; // SingleUser

				IsSystem = (dbId <= 4);
				oRecoveryModel = RecoveryToInt (recovery);

				//Replication settings (sysdatabases.category)
				oPublished = (category & 1) > 0;
				oSubscribed = (category & 2) > 0;
				oMergePublished = (category & 4) > 0;
				oMergeSubscribed = (category & 8) > 0;

				// Reset db-specific values
				dbDataSpaceUsage = 0;
				dbIndexSpaceUsage = 0;
				dbSize = 0;
				dbSpaceAvailable = 0;
				txSize = 0;
				txSpaceAvailable = 0;

				m_track_id = m_track_base + 3;
				cmd = "Select sum(size) / 128.0 from "+dbQName+".dbo.sysfiles";
				dbSize = (Decimal)m_target.ExecuteScalar (cmd);
				cmd = cmd + " WHERE (status & 0x40) <> 0 ";
				txSize = (Decimal)m_target.ExecuteScalar (cmd);
				m_track_id = m_track_base + 4;
                cmd = "Select sum(FILEPROPERTY(name,'SpaceUsed'))/128.0 from " + dbQName + ".dbo.sysfiles WHERE (status & 0x40) <> 0 ";
				txSpaceAvailable = txSize - (Decimal)m_target.ExecuteScalar (cmd);

				m_track_id = m_track_base + 5;
				SqlDataAdapter spaceused_da = new SqlDataAdapter (
					"Select indid, sum(dpages) 'dpages',sum(reserved) 'reserved',sum(used) 'used' from "+
					dbQName+".dbo.sysindexes where indid in (0,1,255) group by indid order by indid ",
					m_target.SQLConnection);
				DataTable spaceused_tab = new DataTable ();
				spaceused_da.Fill (spaceused_tab);
				spaceusedReserved = (long)spaceused_tab.Compute ("Sum(reserved)","");
				spaceusedData = (long)spaceused_tab.Compute ("Sum(dpages)","");
				spaceusedUsed = (long)spaceused_tab.Compute ("Sum(used)","");
				spaceusedText = (long)spaceused_tab.Compute ("Sum(used)","indid=255");

				dbDataSpaceUsage = (spaceusedData + spaceusedText) * 8;
				dbIndexSpaceUsage = (spaceusedUsed - spaceusedText - spaceusedData) * 8;

				unallocated = dbSize - txSize - (Decimal)(spaceusedReserved / 128.0); 
				dbSpaceAvailable = txSpaceAvailable + unallocated;
						
				m_track_id = m_track_base + 6;
				lastBackup = DateTime.Parse ("1/1/1900");
                lastDiffBackup = DateTime.Parse("1/1/1900");
                lastLogBackup = DateTime.Parse("1/1/1900");
				try
				{
					lastBackup = (DateTime)m_target.ExecuteScalar (
						"Select TOP 1 backup_finish_date from msdb.dbo.backupset where database_name = '"+
						FixSingleQuote (dbName)+"' and type = 'D' order by backup_finish_date DESC");
				}
				catch (NullReferenceException) {}

                try
                {
                    lastDiffBackup = (DateTime)m_target.ExecuteScalar(
                        "Select TOP 1 backup_finish_date from msdb.dbo.backupset where database_name = '" +
                        FixSingleQuote(dbName) + "' and type = 'I' order by backup_finish_date DESC");
                }
                catch (NullReferenceException) { }

                try
                {
                    lastLogBackup = (DateTime)m_target.ExecuteScalar(
                        "Select TOP 1 backup_finish_date from msdb.dbo.backupset where database_name = '" +
                        FixSingleQuote(dbName) + "' and type = 'L' order by backup_finish_date DESC");
                }
                catch (NullReferenceException) { }

                SqlCommand sqlcmd = new SqlCommand ();
                sqlcmd.CommandText =
@"Insert dbo.c_database (run_id,srv_id,inst_id,dbid,name,
CompatibilityLevel,CreateDate,DataSpaceUsage,IndexSpaceUsage,dbSize,SpaceAvailable,IsSystem,
txSize,txSpaceAvailable,txLastBackup,txLastLogBackup,txLastDiffBackup,oRecoveryModel,oPageVerify,oUserAccess,
oAutoClose,oAutoShrink,oAutoCreateStat,oAutoUpdateStat,oOffline,oReadOnly,oSelectIntoBulkCopy,oTruncateLogOnCheckpoint,
oPublished,oSubscribed,oMergePublished,oMergeSubscribed,oFullTextEnabled,oDbChaining) 
Values (@run_id,@srv_id,@inst_id,@dbid,@name,
@CompatibilityLevel,@CreateDate,@DataSpaceUsage,@IndexSpaceUsage,@dbSize,@SpaceAvailable,@IsSystem,
@txSize,@txSpaceAvailable,@txLastBackup,@txLastLogBackup,@txLastDiffBackup,@oRecoveryModel,@oPageVerify,@oUserAccess,
@oAutoClose,@oAutoShrink,@oAutoCreateStat,@oAutoUpdateStat,@oOffline,@oReadOnly,@oSelectIntoBulkCopy,@oTruncateLogOnCheckpoint,
@oPublished,@oSubscribed,@oMergePublished,@oMergeSubscribed,@oFullTextEnabled,@oDbChaining) ";
                sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
                sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
                sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
                sqlcmd.Parameters.AddWithValue ("@dbid", dbId);
                sqlcmd.Parameters.AddWithValue ("@name", dbName);
                sqlcmd.Parameters.AddWithValue ("@CompatibilityLevel", compat);
                sqlcmd.Parameters.AddWithValue ("@CreateDate", crdate);
                sqlcmd.Parameters.AddWithValue ("@DataSpaceUsage", (int)dbDataSpaceUsage);
                sqlcmd.Parameters.AddWithValue ("@IndexSpaceUsage", (int)dbIndexSpaceUsage);
                sqlcmd.Parameters.AddWithValue ("@dbSize", Decimal.ToDouble (dbSize));
                sqlcmd.Parameters.AddWithValue ("@SpaceAvailable", Decimal.ToDouble (dbSpaceAvailable));
                sqlcmd.Parameters.AddWithValue ("@IsSystem", IsSystem);
                sqlcmd.Parameters.AddWithValue ("@txSize", Decimal.ToDouble (txSize));
                sqlcmd.Parameters.AddWithValue ("@txSpaceAvailable", Decimal.ToDouble (txSpaceAvailable));
                sqlcmd.Parameters.AddWithValue ("@txLastBackup", lastBackup);
                sqlcmd.Parameters.AddWithValue ("@txLastLogBackup", lastLogBackup);
                sqlcmd.Parameters.AddWithValue ("@txLastDiffBackup", lastDiffBackup);
                sqlcmd.Parameters.AddWithValue ("@oRecoveryModel", oRecoveryModel);
                sqlcmd.Parameters.AddWithValue ("@oPageVerify", oTornPageDetection);
                sqlcmd.Parameters.AddWithValue ("@oUserAccess", oUserAccess);
                sqlcmd.Parameters.AddWithValue ("@oAutoClose", oAutoClose);
                sqlcmd.Parameters.AddWithValue ("@oAutoShrink", oAutoShrink);
                sqlcmd.Parameters.AddWithValue ("@oAutoCreateStat", oAutoCreateStat);
                sqlcmd.Parameters.AddWithValue ("@oAutoUpdateStat", oAutoUpdateStat);
                sqlcmd.Parameters.AddWithValue ("@oOffline", oOffline);
                sqlcmd.Parameters.AddWithValue ("@oReadOnly", oReadOnly);
                sqlcmd.Parameters.AddWithValue ("@oSelectIntoBulkCopy", oSelectIntoBulkCopy);
                sqlcmd.Parameters.AddWithValue ("@oTruncateLogOnCheckpoint", oTruncateLogOnCheckpoint);
                sqlcmd.Parameters.AddWithValue ("@oPublished", oPublished);
                sqlcmd.Parameters.AddWithValue ("@oSubscribed", oSubscribed);
                sqlcmd.Parameters.AddWithValue ("@oMergePublished", oMergePublished);
                sqlcmd.Parameters.AddWithValue ("@oMergeSubscribed", oMergeSubscribed);
                sqlcmd.Parameters.AddWithValue ("@oFullTextEnabled", oFullTextEnabled);
                sqlcmd.Parameters.AddWithValue ("@oDbChaining", oDbChaining);

                ChangeStatus("Adding command for database collection.", StatusEventType.INFO);
				UnitAddData (sqlcmd);

				m_track_id = m_track_base + 10;
				CollectFilegroup2000 (dbId, dbQName);
				CollectDbFiles2000 (dbId, dbQName);
			}
		}

		#region Subordinate Sql2000 Collection functions 

		int RecoveryToInt (string str)
		{
            // Mapped to table d_RecoveryModel

			int oRecoveryModel;

			switch (str)
			{
				case "SIMPLE":
					oRecoveryModel = 3;
					break;
				case "BULK_LOGGED":
					oRecoveryModel = 2;
					break;
				case "FULL":
					oRecoveryModel = 1;
					break;
				default:
					oRecoveryModel = 0;
					break;
			}

			return oRecoveryModel;
		}

        void CollectFilegroup2000 (int a_dbId, string a_dbQName)
        {
            SqlDataAdapter fg_da;
            DataTable fg_tab;

            fg_da = new SqlDataAdapter (
@"SELECT groupid, groupname, 
FILEGROUPPROPERTY( groupname, N'IsReadOnly' ) [IsReadOnly], 
FILEGROUPPROPERTY( groupname, N'IsPrimaryFG' ) [IsPrimary], 
FILEGROUPPROPERTY( groupname, N'IsDefault' ) [IsDefault], 
IsNULL((Select sum(size)*128.0 from " + a_dbQName + @".dbo.sysfiles where groupid = f.groupid),0) [fgSize] 
FROM " + a_dbQName + ".dbo.sysfilegroups f"
                , m_target.SQLConnection);
            fg_tab = new DataTable ();

            fg_da.Fill (fg_tab);

            foreach (DataRow row in fg_tab.Rows)
            {
                SqlCommand sqlcmd = new SqlCommand ();
                sqlcmd.CommandText =
@"Insert dbo.c_filegroup (run_id,srv_id,inst_id,dbid,fgid,name,IsDefault,IsReadOnly,IsOffline,fgSize,type) 
Values (@run_id,@srv_id,@inst_id,@dbid,@fgid,@name,@IsDefault,@IsReadOnly,@IsOffline,@fgSize,@type) ";
                sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
                sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
                sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
                sqlcmd.Parameters.AddWithValue ("@dbid", a_dbId);
                sqlcmd.Parameters.AddWithValue ("@fgid", (short)row["groupid"]);
                sqlcmd.Parameters.AddWithValue ("@name", (string)row["groupname"]);
                sqlcmd.Parameters.AddWithValue ("@IsDefault", ((int)row["IsDefault"] == 1));
                sqlcmd.Parameters.AddWithValue ("@IsReadOnly", ((int)row["IsReadOnly"] == 1));
                sqlcmd.Parameters.AddWithValue ("@IsOffline", false); // always 0 for Sql2000
                sqlcmd.Parameters.AddWithValue ("@fgSize", Decimal.ToDouble ((decimal)row["fgSize"]));
                sqlcmd.Parameters.AddWithValue ("@type", "80"); // Sql2000

                UnitAddData (sqlcmd);
            }
        }

		void CollectDbFiles2000 (int a_dbId, string a_dbQName)
		{
			SqlDataAdapter f_da;
			DataTable f_tab;
			string fName,
				fPhysicalName;
			short fId,
				groupId;
			double fSize,
				fSpaceUsed;
			int fMaxSize,
				fGrowthType = 0,
				fGrowth;
            bool fPrimary,
				fReadOnly,
				fLog;
			uint fStatus;
			
			f_da = new SqlDataAdapter (
@"SELECT fileid, groupid, name, filename, size, maxsize, growth, status, 
FileProperty( name, 'IsPrimaryFile' ) [IsPrimaryFile], 
FileProperty( name, 'IsReadOnly' ) [IsReadOnly], 
FileProperty( name, 'SpaceUsed' ) [SpaceUsed], 
FileProperty( name, 'IsLogFile' ) [IsLog] 
FROM "+a_dbQName+".dbo.sysfiles ",
				m_target.SQLConnection);
			f_tab = new DataTable ();

			f_da.Fill (f_tab);

			foreach (DataRow row in f_tab.Rows)
			{
				fId = (short)row["fileid"];
				groupId = (short)row["groupid"];
				fName = (string)row["name"];
				fPhysicalName = (string)row["filename"];
				fSize = (double)(int)row["size"];
				fMaxSize = (int)row["maxsize"];
				fGrowth = (int)row["growth"];
				fStatus = (uint)(int)row["status"];
				fPrimary = (int)row["IsPrimaryFile"] == 1;
				fReadOnly = (int)row["IsReadOnly"] == 1;
				fSpaceUsed = (int)row["SpaceUsed"];
				fLog = (int)row["IsLog"] == 1;

				fGrowthType = ((fStatus & 0x100000) > 0) ? 1 : 0;
				fSize /= 128; // SizeInMB = NPages * 8 / 1024
				fSpaceUsed /= 128;
				if (fMaxSize != -1) fMaxSize = fMaxSize / 128;
				if (fGrowthType == 0) fGrowth /= 128;

                SqlCommand sqlcmd = new SqlCommand ();
                sqlcmd.CommandText =
@"Insert dbo.c_dbfile (run_id,srv_id,inst_id,dbid,fgid,fid,name,
IsPrimary,IsReadOnly,physicalName,fSize,fSpaceUsed,MaxSize,GrowthType,Growth,IsLog) 
Values (@run_id,@srv_id,@inst_id,@dbid,@fgid,@fid,@name,
@IsPrimary,@IsReadOnly,@physicalName,@fSize,@fSpaceUsed,@MaxSize,@GrowthType,@Growth,@IsLog) ";
                sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
                sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
                sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
                sqlcmd.Parameters.AddWithValue ("@dbid", a_dbId);
                sqlcmd.Parameters.AddWithValue ("@fgid", groupId);
                sqlcmd.Parameters.AddWithValue ("@fid", fId);
                sqlcmd.Parameters.AddWithValue ("@name", fName.Trim ());
                sqlcmd.Parameters.AddWithValue ("@IsPrimary", fPrimary);
                sqlcmd.Parameters.AddWithValue ("@IsReadOnly", fReadOnly);
                sqlcmd.Parameters.AddWithValue ("@physicalName", fPhysicalName.Trim ());
                sqlcmd.Parameters.AddWithValue ("@fSize", fSize);
                sqlcmd.Parameters.AddWithValue ("@fSpaceUsed", fSpaceUsed);
                sqlcmd.Parameters.AddWithValue ("@MaxSize", fMaxSize);
                sqlcmd.Parameters.AddWithValue ("@GrowthType", fGrowthType);
                sqlcmd.Parameters.AddWithValue ("@Growth", fGrowth);
                sqlcmd.Parameters.AddWithValue ("@IsLog", fLog);

				UnitAddData (sqlcmd);
			}
		}

		#endregion 

		#endregion

        void CollectDatabases2005 ()
        {
            SqlDataAdapter db_da;
            DataTable db_tab;
            short dbId,
                compat,
                oPageVerify,
                oUserAccess = 0,
                SnapshotState,
                oRecoveryModel;
            bool oBrokerEnabled,
                IsSystem,
                oAutoClose,
                oAutoShrink,
                oAutoCreateStat,
                oAutoUpdateStat,
                oOffline,
                oReadOnly,
                oSelectIntoBulkCopy = false,
                oTruncateLogOnCheckpoint = false,
                oPublished,
                oSubscribed,
                oMergePublished,
                oMergeSubscribed,
                oFullTextEnabled,
                oDbChaining,
                oSnapshotOn,
                oDistributor,
                mirroringWitness,
                IsRosetta;
            long spaceusedReserved,
                spaceusedUsed,
                spaceusedData,
                dbDataSpaceUsage = 0,
                dbIndexSpaceUsage = 0;
            string cmd,
                dbName,
                dbQName;
            DateTime crdate,
                lastBackup,
                lastDiffBackup,
                lastLogBackup,
                refTime;	// last collection reference time (different form run time as it is stamped within this function)
            Decimal dbSize = 0,
                txSize = 0,
                txSpaceAvailable = 0,
                unallocated = 0,
                dbSpaceAvailable = 0;

            int SourceDbId,
                qIndexedViews;

            byte dbState,
                mirroringSafetyLevel;

            string Collation,
                dbStateDesc;

            if ((m_options & 0x100) > 0)
            {
                QueryRosettaWmi ();
            }

            m_track_id = 2010;

            db_da = new SqlDataAdapter (
@"Select name,QUOTENAME(name) AS [qname],d.database_id,create_date 'modified_date',compatibility_level,
user_access,is_read_only,is_auto_close_on,is_auto_shrink_on,
recovery_model,page_verify_option,is_auto_create_stats_on,is_auto_update_stats_on,
is_fulltext_enabled,is_trustworthy_on,is_db_chaining_on,is_published,is_subscribed,is_merge_published,is_distributor,
state,state_desc,is_broker_enabled,
IsNULL(source_database_id,0) [source_database_id],snapshot_isolation_state, is_read_committed_snapshot_on, collation_name,
IsNULL (m.mirroring_safety_level, 255) [mirroring_safety_level],
(CASE WHEN m.mirroring_witness_name Is NULL THEN 0 ELSE 1 END) [mirroring_witness]
from sys.databases d join sys.database_mirroring m on (d.database_id=m.database_id) ",
                m_target.SQLConnection);
            db_tab = new DataTable ();
            db_da.Fill (db_tab);

            m_track_id = 2011;
            foreach (DataRow row in db_tab.Rows)
            {
                dbId = (short)(int)row["database_id"];
                dbName = (string)row["name"];
                dbQName = (string)row["qname"];
                dbState = (byte)row["state"];

                #region General DB info
                m_track_base = 10000 * dbId + 1000;
                m_track_id = m_track_base + 1;

                if (dbState != 0)
                {
                    dbStateDesc = (string)row["state_desc"];
                    LogMgrError (RunErrorClass.Database, dbId, dbName, CollectorError.SqlDbNotOnline, dbState, dbStateDesc);
                    ChangeStatus ("Database " + dbName + " state is " + dbStateDesc, StatusEventType.INFO);
                    ChangeStatus ("Database-specific information will not be collected", StatusEventType.WARNING);
                    continue;
                }

                try
                {
                    m_target.SQLConnection.ChangeDatabase (dbName);
                }
                catch (SqlException sqlex)
                {
                    if (sqlex.Number == -2)
                    {
                        LogMgrError (RunErrorClass.SqlServer, m_CurrentUnit, m_inst_name, CollectorError.SqlTimeout, sqlex.Number, sqlex.Message);
                        throw;	// Timeout occured - connection is dead, collector can't proceed
                    }
                    else
                    {
                        LogMgrError (RunErrorClass.Database, dbId, dbName, CollectorError.SqlDbInaccessible, sqlex.Number, sqlex.Message);
                        ChangeStatus (sqlex.Message, StatusEventType.ERROR);
                        ChangeStatus ("Database-specific information will not be collected", StatusEventType.WARNING);
                        continue;
                    }
                }

                m_track_id = m_track_base + 2;
                crdate = (DateTime)row["modified_date"];
                compat = (row["compatibility_level"] != DBNull.Value) ? 
                    (short)(byte)row["compatibility_level"] :
                    (short)(byte)m_target.ExecuteScalar ("Select compatibility_level From sys.databases Where database_id=" + dbId.ToString ());
                Collation = (row["collation_name"] != DBNull.Value) ?
                    (string)row["collation_name"] :
                    (string)m_target.ExecuteScalar ("Select collation_name From sys.databases Where database_id=" + dbId.ToString ());
                oRecoveryModel = (short)(byte)row["recovery_model"];
                oAutoClose = (bool)row["is_auto_close_on"];
                oAutoShrink = (bool)row["is_auto_shrink_on"];
                oAutoCreateStat = (bool)row["is_auto_create_stats_on"];
                oAutoUpdateStat = (bool)row["is_auto_update_stats_on"];
                oOffline = (dbState == 6);

                oReadOnly = (bool)row["is_read_only"];
                oPageVerify = (byte)row["page_verify_option"];
                oUserAccess = (byte)row["user_access"];
                oFullTextEnabled = (bool)row["is_fulltext_enabled"];
                oDbChaining = (bool)row["is_db_chaining_on"];

                oBrokerEnabled = (bool)row["is_broker_enabled"];

                SourceDbId = (int)row["source_database_id"];
                SnapshotState = (byte)row["snapshot_isolation_state"];
                oSnapshotOn = (bool)row["is_read_committed_snapshot_on"];

                mirroringSafetyLevel = (byte)row["mirroring_safety_level"];
                mirroringWitness = (int)row["mirroring_witness"] == 1;

                IsSystem = (dbId <= 4);

                //Replication settings 
                oPublished = (bool)row["is_published"];
                oSubscribed = (bool)row["is_subscribed"];
                oMergePublished = (bool)row["is_merge_published"];
                oMergeSubscribed = false;
                oDistributor = (bool)row["is_distributor"];

                // Reset db-specific values
                dbDataSpaceUsage = 0;
                dbIndexSpaceUsage = 0;
                dbSize = 0;
                dbSpaceAvailable = 0;
                txSize = 0;
                txSpaceAvailable = 0;

                // DB size
                m_track_id = m_track_base + 3;
                cmd = "Select (sum(size)) / 128.0 from " + dbQName + ".sys.database_files";
                dbSize = (Decimal)m_target.ExecuteScalar (cmd);

                // Transaction Log size
                if (SourceDbId == 0)	// Snapshot DB doesn't have transaction log
                {
                    cmd = cmd + " WHERE type = 1 "; 
                    txSize = (Decimal)m_target.ExecuteScalar (cmd);
                    m_track_id = m_track_base + 4;
                    cmd = "Select sum(FILEPROPERTY(name,'SpaceUsed'))/128.0 from " + dbQName + ".sys.database_files WHERE type = 1 "; 
                    txSpaceAvailable = txSize - (Decimal)m_target.ExecuteScalar (cmd);
                }

                m_track_id = m_track_base + 5;
                SqlDataAdapter spaceused_da = new SqlDataAdapter (
@"Select sum(a.total_pages) [reserved],sum(a.used_pages) [used],
sum(CASE When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END) [pages] 
from sys.partitions p, sys.allocation_units a where p.partition_id = a.container_id",
                    m_target.SQLConnection);
                spaceused_da.SelectCommand.CommandTimeout = 60;
                DataTable spaceused_tab = new DataTable ();
                spaceused_da.Fill (spaceused_tab);

                DataRow spaceused_row = spaceused_tab.Rows[0];
                spaceusedReserved = (long)spaceused_row["reserved"];
                spaceusedData = (long)spaceused_row["pages"];
                spaceusedUsed = (long)spaceused_row["used"];

                dbDataSpaceUsage = spaceusedData * 8;
                dbIndexSpaceUsage = (spaceusedUsed - spaceusedData) * 8;

                unallocated = dbSize - txSize - (Decimal)(spaceusedReserved / 128.0);
                dbSpaceAvailable = txSpaceAvailable + unallocated;

                m_track_id = m_track_base + 6;
                lastBackup = DateTime.Parse ("1/1/1900");
                lastDiffBackup = DateTime.Parse("1/1/1900");
                lastLogBackup = DateTime.Parse("1/1/1900");
                try
                {
                    cmd = "Select backup_finish_date from msdb.dbo.backupset where database_name = '" +
                        FixSingleQuote(dbName) + "' and type = 'D' order by backup_finish_date DESC";
                    lastBackup = (DateTime)m_target.ExecuteScalar (cmd);
                }
                catch (NullReferenceException) { }
                catch (Exception ex)
                {
                    ChangeStatus (ex.GetType ().ToString () + ": " + ex.Message, StatusEventType.DEBUG);
                    ChangeStatus (cmd, StatusEventType.DEBUG);
                    continue;
                }

                try
                {
                    lastDiffBackup = (DateTime)m_target.ExecuteScalar(
                        "Select backup_finish_date from msdb.dbo.backupset where database_name = '" +
                        FixSingleQuote(dbName) + "' and type = 'I' order by backup_finish_date DESC");
                }
                catch (NullReferenceException) { }
                catch (Exception ex)
                {
                    ChangeStatus(ex.GetType().ToString() + ": " + ex.Message, StatusEventType.DEBUG);
                    ChangeStatus(cmd, StatusEventType.DEBUG);
                    continue;
                }

                try
                {
                    lastLogBackup = (DateTime)m_target.ExecuteScalar(
                        "Select backup_finish_date from msdb.dbo.backupset where database_name = '" +
                        FixSingleQuote(dbName) + "' and type = 'L' order by backup_finish_date DESC");
                }
                catch (NullReferenceException) { }
                catch (Exception ex)
                {
                    ChangeStatus(ex.GetType().ToString() + ": " + ex.Message, StatusEventType.DEBUG);
                    ChangeStatus(cmd, StatusEventType.DEBUG);
                    continue;
                }

                m_track_id = m_track_base + 7;
                SqlCommand sqlcmd = new SqlCommand ();
                sqlcmd.CommandText =
@"Insert dbo.c_database (run_id,srv_id,inst_id,dbid,name,
CompatibilityLevel,CreateDate,DataSpaceUsage,IndexSpaceUsage,dbSize,SpaceAvailable,IsSystem,
txSize,txSpaceAvailable,txLastBackup,txLastLogBackup,txLastDiffBackup,oRecoveryModel,oPageVerify,oUserAccess,
oAutoClose,oAutoShrink,oAutoCreateStat,oAutoUpdateStat,
oOffline,oReadOnly,oSelectIntoBulkCopy,oTruncateLogOnCheckpoint,
oPublished,oSubscribed,oMergePublished,oMergeSubscribed,
oFullTextEnabled,oDbChaining) 
Values (@run_id,@srv_id,@inst_id,@dbid,@name,
@CompatibilityLevel,@CreateDate,@DataSpaceUsage,@IndexSpaceUsage,@dbSize,@SpaceAvailable,@IsSystem,
@txSize,@txSpaceAvailable,@txLastBackup,@txLastLogBackup,@txLastDiffBackup,@oRecoveryModel,@oPageVerify,@oUserAccess,
@oAutoClose,@oAutoShrink,@oAutoCreateStat,@oAutoUpdateStat,
@oOffline,@oReadOnly,@oSelectIntoBulkCopy,@oTruncateLogOnCheckpoint,
@oPublished,@oSubscribed,@oMergePublished,@oMergeSubscribed,
@oFullTextEnabled,@oDbChaining) ";
                sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
                sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
                sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
                sqlcmd.Parameters.AddWithValue ("@dbid", dbId);
                sqlcmd.Parameters.AddWithValue ("@name", dbName);
                sqlcmd.Parameters.AddWithValue ("@CompatibilityLevel", compat);
                sqlcmd.Parameters.AddWithValue ("@CreateDate", crdate);
                sqlcmd.Parameters.AddWithValue ("@DataSpaceUsage", dbDataSpaceUsage);
                sqlcmd.Parameters.AddWithValue ("@IndexSpaceUsage", dbIndexSpaceUsage);
                sqlcmd.Parameters.AddWithValue ("@dbSize", Decimal.ToDouble (dbSize));
                sqlcmd.Parameters.AddWithValue ("@SpaceAvailable", Decimal.ToDouble (dbSpaceAvailable));
                sqlcmd.Parameters.AddWithValue ("@IsSystem", IsSystem);
                sqlcmd.Parameters.AddWithValue ("@txSize", Decimal.ToDouble (txSize));
                sqlcmd.Parameters.AddWithValue ("@txSpaceAvailable", Decimal.ToDouble (txSpaceAvailable));
                sqlcmd.Parameters.AddWithValue ("@txLastBackup", lastBackup);
                sqlcmd.Parameters.AddWithValue("@txLastLogBackup", lastLogBackup);
                sqlcmd.Parameters.AddWithValue("@txLastDiffBackup", lastDiffBackup);
                sqlcmd.Parameters.AddWithValue ("@oRecoveryModel", oRecoveryModel);
                sqlcmd.Parameters.AddWithValue ("@oPageVerify", oPageVerify);
                sqlcmd.Parameters.AddWithValue ("@oUserAccess", oUserAccess);
                sqlcmd.Parameters.AddWithValue ("@oAutoClose", oAutoClose);
                sqlcmd.Parameters.AddWithValue ("@oAutoShrink", oAutoShrink);
                sqlcmd.Parameters.AddWithValue ("@oAutoCreateStat", oAutoCreateStat);
                sqlcmd.Parameters.AddWithValue ("@oAutoUpdateStat", oAutoUpdateStat);
                sqlcmd.Parameters.AddWithValue ("@oOffline", oOffline);
                sqlcmd.Parameters.AddWithValue ("@oReadOnly", oReadOnly);
                sqlcmd.Parameters.AddWithValue ("@oSelectIntoBulkCopy", oSelectIntoBulkCopy);
                sqlcmd.Parameters.AddWithValue ("@oTruncateLogOnCheckpoint", oTruncateLogOnCheckpoint);
                sqlcmd.Parameters.AddWithValue ("@oPublished", oPublished);
                sqlcmd.Parameters.AddWithValue ("@oSubscribed", oSubscribed);
                sqlcmd.Parameters.AddWithValue ("@oMergePublished", oMergePublished);
                sqlcmd.Parameters.AddWithValue ("@oMergeSubscribed", oMergeSubscribed);
                sqlcmd.Parameters.AddWithValue ("@oFullTextEnabled", oFullTextEnabled);
                sqlcmd.Parameters.AddWithValue ("@oDbChaining", oDbChaining);

                UnitAddData (sqlcmd);

                m_track_id = m_track_base + 10;
                CollectAllocations (dbId, dbQName);

                #endregion

                if ((m_options & 0x100) == 0) continue;

                refTime = GetRefTime (dbId);

                CollectDbObjects (dbId, dbName, dbQName, refTime);

                if (m_RosettaDb_array.Count == 0 || m_RosettaDb_array.IndexOf (dbName) == -1)
                {
                    IsRosetta = false;
                }
                else
                {
                    IsRosetta = true;
                    CollectRosetta (dbId, dbQName, refTime);
                }

                if (m_MailHostDbId == 0)
                {
                    CollectMailHost (dbId, dbQName);
                }

                qIndexedViews = (int)m_target.ExecuteScalar (
"Select count(*) from " + dbQName + ".sys.views v join " + dbQName + ".sys.indexes i on (v.object_id=i.object_id)");

                SqlCommand sqlcmd2 = new SqlCommand ();
                sqlcmd2.CommandText = 
@"Insert dbo.c_database2 (run_id,srv_id,inst_id,dbid,
oBrokerEnabled,oReadCommittedSnapshotOn,SnapshotIsolationState,Collation,oDistributor,
qIndexedViews,mirroringSafetyLevel,mirroringWitness,IsRosetta) 
Values (@run_id,@srv_id,@inst_id,@dbid,@oBrokerEnabled,@oReadCommittedSnapshotOn,@SnapshotIsolationState,
@Collation,@oDistributor,@qIndexedViews,@mirroringSafetyLevel,@mirroringWitness,@IsRosetta) ";
                sqlcmd2.Parameters.AddWithValue ("@run_id", m_rd.run_id);
                sqlcmd2.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
                sqlcmd2.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
                sqlcmd2.Parameters.AddWithValue ("@dbid", dbId);
                sqlcmd2.Parameters.AddWithValue ("@oBrokerEnabled", oBrokerEnabled);
                sqlcmd2.Parameters.AddWithValue ("@oReadCommittedSnapshotOn", oSnapshotOn);
                sqlcmd2.Parameters.AddWithValue ("@SnapshotIsolationState", SnapshotState);
                sqlcmd2.Parameters.AddWithValue ("@Collation", Collation);
                sqlcmd2.Parameters.AddWithValue ("@oDistributor", oDistributor);
                sqlcmd2.Parameters.AddWithValue ("@qIndexedViews", qIndexedViews);
                sqlcmd2.Parameters.AddWithValue ("@mirroringSafetyLevel", mirroringSafetyLevel);
                sqlcmd2.Parameters.AddWithValue ("@mirroringWitness", mirroringWitness);
                sqlcmd2.Parameters.AddWithValue ("@IsRosetta", IsRosetta);

                UnitAddData (sqlcmd2);
            }

            m_target.SQLConnection.ChangeDatabase ("master");
        }

		#region Allocations
		void CollectAllocations (int a_dbId, string a_dbQName)
		{
			if ((m_options & 0x100) > 0) 
			{
				CollectDataspaces (a_dbId, a_dbQName);
				CollectPartitionSchemes (a_dbId, a_dbQName);
				CollectPartitionFunctions (a_dbId, a_dbQName);
				CollectPartitionParameters (a_dbId, a_dbQName);
			}
			CollectFilegroup2005 (a_dbId, a_dbQName);
			CollectDbFiles2005 (a_dbId, a_dbQName);
		}

		void CollectFilegroup2005 (int a_dbId, string a_dbQName)
		{
			SqlDataAdapter fg_da;
			DataTable fg_tab;
			string fgName,
				fgType;
			short fgId;
            bool fgReadOnly,
                fgDefault,
                fgOffline = false; //is_offline removed from Sql2005
			double fgSize;

			fg_da = new SqlDataAdapter (
@"Select name,data_space_id,type,is_default,is_read_only, 
IsNULL((Select sum(size)*128.0 From "+a_dbQName+@".sys.database_files f Where f.data_space_id=g.data_space_id),0) [fgSize] 
From "+a_dbQName+".sys.filegroups g "
				, m_target.SQLConnection);
			fg_tab = new DataTable ();

			fg_da.Fill (fg_tab);
			foreach (DataRow row in fg_tab.Rows)
			{
				fgId = (short)(int)row["data_space_id"];
				fgName = (string)row["name"];
				fgSize = Decimal.ToDouble ((decimal)row["fgSize"]);
				fgReadOnly = (bool)row["is_read_only"];
				fgDefault = (bool)row["is_default"];
				fgType = (string)row["type"];

                SqlCommand sqlcmd = new SqlCommand ();
                sqlcmd.CommandText =
@"Insert dbo.c_filegroup (run_id,srv_id,inst_id,dbid,fgid,name,IsDefault,IsReadOnly,IsOffline,fgSize,type) 
Values (@run_id,@srv_id,@inst_id,@dbid,@fgid,@name,@IsDefault,@IsReadOnly,@IsOffline,@fgSize,@type) ";
                sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
                sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
                sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
                sqlcmd.Parameters.AddWithValue ("@dbid", a_dbId);
                sqlcmd.Parameters.AddWithValue ("@fgid", fgId);
                sqlcmd.Parameters.AddWithValue ("@name", fgName);
                sqlcmd.Parameters.AddWithValue ("@IsDefault", fgDefault);
                sqlcmd.Parameters.AddWithValue ("@IsReadOnly", fgReadOnly);
                sqlcmd.Parameters.AddWithValue ("@IsOffline", fgOffline);
                sqlcmd.Parameters.AddWithValue ("@fgSize", fgSize);
                sqlcmd.Parameters.AddWithValue ("@type", fgType);
                
				UnitAddData (sqlcmd);
			}
		}

		void CollectDataspaces (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
			string dsName,
				dsType;
            int dsId;
            bool dsDefault;


			command = new SqlCommand ("Select name, data_space_id, type, is_default from "+a_dbQName+".sys.data_spaces", 
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					dsName = dr.GetString (0);
					dsId = dr.GetInt32 (1);
					dsType = dr.GetString (2);
					dsDefault = dr.GetBoolean (3);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_dataspace (run_id,srv_id,inst_id,dbid,dsid,name,IsDefault,type) 
Values (@run_id,@srv_id,@inst_id,@dbid,@dsid,@name,@IsDefault,@type) ";
                    sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
                    sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
                    sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
                    sqlcmd.Parameters.AddWithValue ("@dbid", a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@dsid", dsId);
                    sqlcmd.Parameters.AddWithValue ("@name", dsName);
                    sqlcmd.Parameters.AddWithValue ("@IsDefault", dsDefault);
                    sqlcmd.Parameters.AddWithValue ("@type", dsType);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectPartitionSchemes (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
			int dsId,
				funcId;


			command = new SqlCommand ("Select data_space_id, function_id from "+a_dbQName+".sys.partition_schemes", 
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					dsId = dr.GetInt32 (0);
					funcId = dr.GetInt32 (1);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText = @"Insert dbo.c_partition_scheme (run_id,srv_id,inst_id,dbid,dsid,fnId) 
Values (@run_id,@srv_id,@inst_id,@dbid,@dsid,@fnId) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@dsid", dsId);
                    sqlcmd.Parameters.AddWithValue ("@fnId", funcId);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectPartitionFunctions (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
			string fnType;
			int fanout,
				fnId;
            bool boundary;


			command = new SqlCommand ("Select function_id,type,fanout,boundary_value_on_right "
				+"from "+a_dbQName+".sys.partition_functions ", 
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					fnId = dr.GetInt32 (0);
					fnType = dr.GetString (1);
					fanout = dr.GetInt32 (2);
					boundary = dr.GetBoolean (3);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText = @"Insert dbo.c_partition_function (run_id,srv_id,inst_id,dbid,fnId,type,fanout,boundary_value_on_right) 
Values (@run_id,@srv_id,@inst_id,@dbid,@fnId,@type,@fanout,@boundary_value_on_right) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@fnId", fnId);
                    sqlcmd.Parameters.AddWithValue ("@type", fnType);
                    sqlcmd.Parameters.AddWithValue ("@fanout", fanout);
                    sqlcmd.Parameters.AddWithValue ("@boundary_value_on_right", boundary);
                    
					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectPartitionParameters (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
			string Collation;
			int fpTypeId,
				fpLength,
				fpPrecision,
				fpScale,
				q;


			command = new SqlCommand ("Select system_type_id, max_length, precision, scale, IsNULL(collation_name,''), count(*) from "
                +a_dbQName+".sys.partition_parameters group by system_type_id, max_length, precision, scale, collation_name ",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					fpTypeId = dr.GetByte (0);
					fpLength = dr.GetInt16 (1);
					fpPrecision = dr.GetByte (2);
					fpScale = dr.GetByte (3);
					Collation = dr.GetString (4);
					q = dr.GetInt32 (5);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText = 
@"Insert dbo.c_partition_parameters (run_id,srv_id,inst_id,dbid,system_type_id,max_length,prec,scale,collation_name,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@system_type_id,@max_length,@prec,@scale,@collation_name,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue("@system_type_id", fpTypeId);
                    sqlcmd.Parameters.AddWithValue("@max_length", fpLength);
                    sqlcmd.Parameters.AddWithValue("@prec", fpPrecision);
                    sqlcmd.Parameters.AddWithValue("@scale", fpScale);
                    sqlcmd.Parameters.AddWithValue("@collation_name", Collation);
                    sqlcmd.Parameters.AddWithValue("@qObjects", q);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectDbFiles2005 (int a_dbId, string a_dbQName)
		{
			SqlDataAdapter f_da;
			DataTable f_tab;
			string fName,
				fPhysicalName;
			double fSize,
				fSpaceUsed;
			int fId,
				fMaxSize,
				fGrowth;
            short groupId,
				fGrowthType = 0;
            bool fPrimary,
                fReadOnly,
				fLog;

			f_da = new SqlDataAdapter (
@"SELECT file_id,IsNULL(data_space_id,0) [data_space_id],
name,physical_name,size,max_size,growth,is_read_only,is_percent_growth,type, 
IsNULL(FileProperty( name, 'IsPrimaryFile' ), 0) [IsPrimaryFile], 
IsNULL(FileProperty( name, 'SpaceUsed' ), 0) [SpaceUsed] 
FROM "+a_dbQName+".sys.database_files ",
				m_target.SQLConnection);
			f_tab = new DataTable ();

			f_da.Fill (f_tab);

			foreach (DataRow row in f_tab.Rows)
			{
				fId = (int)row["file_id"];
				groupId = (short)(int)row["data_space_id"];
				fName = (string)row["name"];
				fPhysicalName = (string)row["physical_name"];
				fSize = (double)(int)row["size"];
				fMaxSize = (int)row["max_size"];
				fGrowth = (int)row["growth"];
				fPrimary = ((int)row["IsPrimaryFile"] == 1);
				fReadOnly = (bool)row["is_read_only"];
				fSpaceUsed = (int)row["SpaceUsed"];
				
				fLog = ((byte)row["type"] == 1);

				fGrowthType = (short)((bool)row["is_percent_growth"] ? 1 : 0);
				fSize /= 128; // SizeInMB = NPages * 8 / 1024
				fSpaceUsed /= 128;
				if (fMaxSize != -1) fMaxSize = fMaxSize / 128;
				if (fGrowthType == 0) fGrowth /= 128;

                SqlCommand sqlcmd = new SqlCommand ();
                sqlcmd.CommandText =
@"Insert dbo.c_dbfile (run_id,srv_id,inst_id,dbid,fgid,fid,name,
IsPrimary,IsReadOnly,physicalName,fSize,fSpaceUsed,MaxSize,GrowthType,Growth,IsLog) 
Values (@run_id,@srv_id,@inst_id,@dbid,@fgid,@fid,@name,
@IsPrimary,@IsReadOnly,@physicalName,@fSize,@fSpaceUsed,@MaxSize,@GrowthType,@Growth,@IsLog) ";
                sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
                sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
                sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
                sqlcmd.Parameters.AddWithValue ("@dbid", a_dbId);
                sqlcmd.Parameters.AddWithValue ("@fgid", groupId);
                sqlcmd.Parameters.AddWithValue ("@fid", fId);
                sqlcmd.Parameters.AddWithValue ("@name", fName.Trim ());
                sqlcmd.Parameters.AddWithValue ("@IsPrimary", fPrimary);
                sqlcmd.Parameters.AddWithValue ("@IsReadOnly", fReadOnly);
                sqlcmd.Parameters.AddWithValue ("@physicalName", fPhysicalName.Trim ());
                sqlcmd.Parameters.AddWithValue ("@fSize", fSize);
                sqlcmd.Parameters.AddWithValue ("@fSpaceUsed", fSpaceUsed);
                sqlcmd.Parameters.AddWithValue ("@MaxSize", fMaxSize);
                sqlcmd.Parameters.AddWithValue ("@GrowthType", fGrowthType);
                sqlcmd.Parameters.AddWithValue ("@Growth", fGrowth);
                sqlcmd.Parameters.AddWithValue ("@IsLog", fLog);

                UnitAddData (sqlcmd);
			}
		}

		#endregion

		#region DB Objects
		void CollectDbObjects (int a_dbId, string a_dbName, string a_dbQName, DateTime a_refTime)
		{
			CollectObjects (a_dbId, a_dbQName, a_refTime);
			CollectIndexes (a_dbId, a_dbQName);
			CollectTriggers (a_dbId, a_dbQName);
			CollectInternalTabs (a_dbId, a_dbQName);
			CollectAssemblies (a_dbId, a_dbQName);
			CollectAssemblyTypes (a_dbId, a_dbQName);
			CollectColumns (a_dbId, a_dbQName);
			CollectEventNotifications (a_dbId, a_dbQName);

			try
			{
				CollectNotificationSubscriptions (a_dbId, a_dbQName);
			}
			catch (Exception ex)
			{
				if (m_debug_ns)
				{
					ChangeStatus (ex.Message, StatusEventType.ERROR); 
					ChangeStatus ("exception in DMV block (notification_subscriptions)", StatusEventType.DEBUG);
					m_debug_ns = false;
				}
			}

            CollectFullText (a_dbId, a_dbQName);
			CollectServiceBroker (a_dbId, a_dbQName);
			CollectBackups (a_dbId, a_dbName, a_refTime);   // unquoted name is passed (it is stored in the record)
			CollectTextInRow (a_dbId, a_dbQName);
		}

		void CollectObjects (int a_dbId, string a_dbQName, DateTime lastCollection)
		{
			SqlDataReader dr;
			SqlCommand command;
			string objType;
			int q,
				qCreated,
				qModified;
            bool objMsShipped,
                objPublished;
				 
			
			command = new SqlCommand (
@"Select type, is_ms_shipped, is_published, count(*) [Count], 
(Select count(*) from " + a_dbQName + @".sys.objects i 
where o.type=i.type and o.is_ms_shipped=i.is_ms_shipped and o.is_published=i.is_published 
and create_date > @lastCollection) [Created], 
(Select count(*) from " + a_dbQName + @".sys.objects i 
where o.type=i.type and o.is_ms_shipped=i.is_ms_shipped and o.is_published=i.is_published 
and modify_date > @lastCollection) [Modified] 
from " +a_dbQName+".sys.objects o group by type, is_ms_shipped, is_published ", 
				m_target.SQLConnection);
			command.CommandTimeout = 60;
            command.Parameters.AddWithValue ("@lastCollection", lastCollection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					objType = dr.GetString (0);
					objMsShipped = dr.GetBoolean (1);
					objPublished = dr.GetBoolean (2);
					q = dr.GetInt32 (3);
					qCreated = dr.GetInt32 (4);
					qModified = dr.GetInt32 (5);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_dbobjects (run_id,srv_id,inst_id,dbid,type,isMsShipped,isPublished,qObjects,qCreated,qModified) 
Values (@run_id,@srv_id,@inst_id,@dbid,@type,@isMsShipped,@isPublished,@qObjects,@qCreated,@qModified) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue("@type", objType);
                    sqlcmd.Parameters.AddWithValue("@isMsShipped", objMsShipped);
                    sqlcmd.Parameters.AddWithValue("@isPublished", objPublished);
                    sqlcmd.Parameters.AddWithValue("@qObjects", q);
                    sqlcmd.Parameters.AddWithValue("@qCreated", qCreated);
                    sqlcmd.Parameters.AddWithValue("@qModified", qModified);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		DateTime GetRefTime (int a_dbId)
		{
            SqlCommand command = new SqlCommand (
@"Select IsNULL(max(reftime), 0) From mgr_dbobject_reftime Where srv_id=@srv_id and inst_id=@inst_id and dbId=@dbId ",
               m_repository.SQLConnection);
            command.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            command.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
            command.Parameters.AddWithValue ("@dbId", a_dbId);

            DateTime dt = (DateTime)command.ExecuteScalar ();

            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.CommandText =
@"Insert mgr_dbobject_reftime (run_id,srv_id,inst_id,dbId,reftime,prev_reftime) 
Values (@run_id,@srv_id,@inst_id,@dbId,@reftime,@prev_reftime) ";
            sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
            sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
            sqlcmd.Parameters.AddWithValue ("@dbId", a_dbId);
            sqlcmd.Parameters.AddWithValue ("@reftime", DateTime.Now); 
            sqlcmd.Parameters.AddWithValue ("@prev_reftime", dt);

			UnitAddData (sqlcmd);

			return dt;
		}

		void CollectIndexes (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
            byte type;
            bool isUnique,
                isConstraint;
			int q;
			
			command = new SqlCommand (
@"Select type, is_unique, CASE WHEN is_primary_key=1 OR is_unique_constraint=1 THEN 1 ELSE 0 END is_constraint, count(*) 
from "+a_dbQName+".sys.indexes group by type, is_unique, CASE WHEN is_primary_key=1 OR is_unique_constraint=1 THEN 1 ELSE 0 END ", 
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					type = dr.GetByte (0);
					isUnique = dr.GetBoolean (1);
					isConstraint = dr.GetInt32 (2) == 1;
					q = dr.GetInt32 (3);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_indexes (run_id,srv_id,inst_id,dbid,type,is_unique,is_constraint,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@type,@is_unique,@is_constraint,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@type", type);
                    sqlcmd.Parameters.AddWithValue ("@is_unique", isUnique);
                    sqlcmd.Parameters.AddWithValue ("@is_constraint", isConstraint);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectTriggers (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
			string type;
            short parentClass;
            bool isDisabled,
                isInsteadOf;
			int q;
			
			command = new SqlCommand (
@"Select parent_class, type, is_disabled, is_instead_of_trigger, count(*) [Count] 
from "+a_dbQName+".sys.triggers o group by parent_class, type, is_disabled, is_instead_of_trigger ", 
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
                    parentClass = dr.GetByte (0);
					type = dr.GetString (1);
					isDisabled = dr.GetBoolean (2);
                    isInsteadOf = dr.GetBoolean (3);
					q = dr.GetInt32 (4);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText = 
@"Insert dbo.c_triggers (run_id,srv_id,inst_id,dbid,parentClass,type,isDisabled,isInsteadOf,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@parentClass,@type,@isDisabled,@isInsteadOf,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@parentClass", parentClass);
                    sqlcmd.Parameters.AddWithValue ("@type", type);
                    sqlcmd.Parameters.AddWithValue ("@isDisabled", isDisabled);
                    sqlcmd.Parameters.AddWithValue ("@isInsteadOf", isInsteadOf);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectInternalTabs (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
            short NamespaceClass;
			int ParentClass,
				q;
			
			command = new SqlCommand (
@"With int_tabs_with_rows as 
(Select internal_type, (Select avg(rows) From "+a_dbQName+@".sys.partitions p 
Where p.object_id=it.object_id) avg_rows From "+a_dbQName+@".sys.internal_tables it) 
Select internal_type, avg(avg_rows) avg_rows, count(*) 'q' From int_tabs_with_rows 
group by internal_type",
			    m_target.SQLConnection);

            dr = command.ExecuteReader ();
			try
			{
                while (dr.Read ())
                {
                    NamespaceClass = dr.GetByte (0);
                    ParentClass = (int)dr.GetInt64 (1);
                    q = dr.GetInt32 (2);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_internal_tables (run_id,srv_id,inst_id,dbid,NamespaceClass,ParentClass,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@NamespaceClass,@ParentClass,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@NamespaceClass", NamespaceClass);
                    sqlcmd.Parameters.AddWithValue ("@ParentClass", ParentClass);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

                    UnitAddData (sqlcmd);
                }
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectAssemblies (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
            short PermissionSet;
            bool isVisible;
			int q;
			
			command = new SqlCommand (
"Select permission_set, is_visible, count(*) from "+a_dbQName+@".sys.assemblies 
group by permission_set, is_visible ",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
                while (dr.Read ())
                {
                    PermissionSet = dr.GetByte (0);
                    isVisible = dr.GetBoolean (1);
                    q = dr.GetInt32 (2);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_assemblies (run_id,srv_id,inst_id,dbid,permission_set,is_visible,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@permission_set,@is_visible,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@permission_set", PermissionSet);
                    sqlcmd.Parameters.AddWithValue ("@is_visible", isVisible);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

                    UnitAddData (sqlcmd);
                }
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectAssemblyTypes (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
			short maxLength;
            bool isNullable,
                isUserDefined,
                isAssemblyType,
                isBinaryOrdered,
                isFixedLength;
            int q;
			
			command = new SqlCommand (
@"Select max_length, is_nullable, is_user_defined, is_assembly_type, is_binary_ordered, is_fixed_length, count(*) 
from "+a_dbQName+@".sys.assembly_types 
group by max_length, is_nullable, is_user_defined, is_assembly_type, is_binary_ordered, is_fixed_length ",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					maxLength = dr.GetInt16 (0);
					isNullable = dr.GetBoolean (1);
					isUserDefined = dr.GetBoolean (2);
					isAssemblyType = dr.GetBoolean (3); 
					isBinaryOrdered = dr.GetBoolean (4);
					isFixedLength = dr.GetBoolean (5);
					q = dr.GetInt32 (6);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_assembly_types (run_id,srv_id,inst_id,dbid,max_length,is_nullable,is_user_defined,is_assembly_type,is_binary_ordered,is_fixed_length,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@max_length,@is_nullable,@is_user_defined,@is_assembly_type,@is_binary_ordered,@is_fixed_length,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@max_length", maxLength);
                    sqlcmd.Parameters.AddWithValue ("@is_nullable", isNullable);
                    sqlcmd.Parameters.AddWithValue ("@is_user_defined", isUserDefined);
                    sqlcmd.Parameters.AddWithValue ("@is_assembly_type", isAssemblyType);
                    sqlcmd.Parameters.AddWithValue ("@is_binary_ordered", isBinaryOrdered);
                    sqlcmd.Parameters.AddWithValue ("@is_fixed_length", isFixedLength);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);
                    
					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectColumns (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
			string Collation;
			int cTypeId,
				cUserTypeId,
				cLength,
				cPrecision,
				cScale,
				cNull,
				cPadded,
				cGUID,
				cComputed,
				cFileStream,
				cXml,
				cXmlCol,
				cXmlNS,
				q;
			
			command = new SqlCommand (
@"Select system_type_id, user_type_id, max_length, precision, scale, 
IsNULL(collation_name,''),is_nullable, is_ansi_padded, is_rowguidcol, is_computed, is_filestream, is_xml_document, 
Case when xml_collection_id is null then 0 else 1 end, 0, count(*) 
From "+a_dbQName+@".sys.columns 
Group by system_type_id, user_type_id, max_length, precision, scale, collation_name, 
is_nullable, is_ansi_padded, is_rowguidcol, is_computed, is_filestream, is_xml_document, 
case when xml_collection_id is null then 0 else 1 end ",
				m_target.SQLConnection);
			command.CommandTimeout = 60;

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					cTypeId = dr.GetByte (0);
					cUserTypeId = dr.GetInt32 (1);
					cLength = dr.GetInt16 (2);
					cPrecision = dr.GetByte (3);
					cScale = dr.GetByte (4);
					Collation = dr.GetString (5);
					cNull = dr.GetBoolean (6) ? 1 : 0;
					cPadded = dr.GetBoolean (7) ? 1 : 0;
					cGUID = dr.GetBoolean (8) ? 1 : 0; 
					cComputed = dr.GetBoolean (9) ? 1 : 0;
					cFileStream = dr.GetBoolean (10) ? 1 : 0;
					cXml = dr.GetBoolean (11) ? 1 : 0;
					cXmlCol = dr.GetInt32 (12);
					cXmlNS = dr.GetInt32 (13);
					q = dr.GetInt32 (14);

//spri_column @run_id int, @srv_id int, @inst_id int, @dbid int, @q int,
//@system_type_id int, @user_type_id int, @max_length int, @prec int, @scale int, 
//@collation_name sysname, @is_nullable int, @is_ansi_padded int, @is_rowguidcol int, 
//@is_computed int, @is_filestream int, @is_xml_document int, @is_xml_collection int, @is_xml_namespace int

                    SqlCommand sqlcmd = new SqlCommand ("dbo.spri_column");
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@q", q);
                    sqlcmd.Parameters.AddWithValue ("@system_type_id", cTypeId);
                    sqlcmd.Parameters.AddWithValue ("@user_type_id", cUserTypeId);
                    sqlcmd.Parameters.AddWithValue ("@max_length", cLength);
                    sqlcmd.Parameters.AddWithValue ("@prec", cPrecision);
                    sqlcmd.Parameters.AddWithValue ("@scale", cScale);
                    sqlcmd.Parameters.AddWithValue ("@collation_name", Collation);
                    sqlcmd.Parameters.AddWithValue ("@is_nullable", cNull);
                    sqlcmd.Parameters.AddWithValue ("@is_ansi_padded", cPadded);
                    sqlcmd.Parameters.AddWithValue ("@is_rowguidcol", cGUID);
                    sqlcmd.Parameters.AddWithValue ("@is_computed", cComputed);
                    sqlcmd.Parameters.AddWithValue ("@is_filestream", cFileStream);
                    sqlcmd.Parameters.AddWithValue ("@is_xml_document",cXml );
                    sqlcmd.Parameters.AddWithValue ("@is_xml_collection", cXmlCol);
                    sqlcmd.Parameters.AddWithValue ("@is_xml_namespace", cXmlNS);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectEventNotifications (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
            short parentClass;
			int	q;
			
			command = new SqlCommand (
"Select parent_class, count(*) from "+a_dbQName+".sys.event_notifications group by parent_class ", 
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					parentClass = dr.GetByte (0);
					q = dr.GetInt32 (1);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_event_notifications (run_id,srv_id,inst_id,dbid,parentClass,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@parentClass,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@parentClass", parentClass);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectNotificationSubscriptions (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
			int objId,
				status,
				q;
			
			command = new SqlCommand (
"Select object_id, status, count(*) from "+a_dbQName+".sys.dm_qn_subscriptions group by object_id, status ", 
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					objId = dr.GetInt32 (0);
					status = dr.GetInt32 (1);
					q = dr.GetInt32 (2);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_notification_subscriptions (run_id,srv_id,inst_id,dbid,objectId,status,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@objectId,@status,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@objectId", objId);
                    sqlcmd.Parameters.AddWithValue ("@status", status);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectServiceBroker (int a_dbId, string a_dbQName)
		{
			int Routes,
				Queues,
				ActivatedQueues,
				MessageTypes,
				TypedMessageTypes;

			Routes = (int)m_target.ExecuteScalar ("Select count(*) from "+a_dbQName+".sys.routes");
			Queues = (int)m_target.ExecuteScalar ("Select count(*) from "+a_dbQName+".sys.service_queues where is_ms_shipped=0");
			ActivatedQueues = (int)m_target.ExecuteScalar ("Select count(*) from "+a_dbQName+".sys.service_queues where is_ms_shipped=0 and is_activation_enabled=1");
			MessageTypes = (int)m_target.ExecuteScalar ("Select count(*) from "+a_dbQName+".sys.service_message_types where message_type_id>65535");
			TypedMessageTypes = (int)m_target.ExecuteScalar ("Select count(xml_collection_id) from "+a_dbQName+".sys.service_message_types where message_type_id>65535");

            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.CommandText =
@"Insert dbo.c_sb (run_id,srv_id,inst_id,dbid,Routes,Queues,ActivatedQueues,MessageTypes,TypedMessageTypes) 
Values (@run_id,@srv_id,@inst_id,@dbid,@Routes,@Queues,@ActivatedQueues,@MessageTypes,@TypedMessageTypes) ";
            AddInstanceDbParams (sqlcmd, a_dbId);
            sqlcmd.Parameters.AddWithValue ("@Routes", Routes);
            sqlcmd.Parameters.AddWithValue ("@Queues", Queues);
            sqlcmd.Parameters.AddWithValue ("@ActivatedQueues", ActivatedQueues);
            sqlcmd.Parameters.AddWithValue ("@MessageTypes", MessageTypes);
            sqlcmd.Parameters.AddWithValue ("@TypedMessageTypes", TypedMessageTypes);
            
			UnitAddData (sqlcmd);
		}

		void CollectFullText (int a_dbId, string a_dbQName)
		{
			int ftCatalogs,
				ftTables,
				ftBackgroundIndexes;
			DateTime ftLastCrawl;

            ftCatalogs = (int)m_target.ExecuteScalar ("Select COUNT(fulltext_catalog_id) From " + a_dbQName + ".sys.fulltext_catalogs");
            ftTables = (int)m_target.ExecuteScalar ("SELECT count(distinct t.name) FROM " + a_dbQName + ".sys.fulltext_indexes f join " + a_dbQName + ".sys.tables t on (f.object_id=t.object_id) WHERE f.is_enabled=1");
            ftLastCrawl = (DateTime)m_target.ExecuteScalar ("Select IsNULL(MAX(crawl_start_date) ,0) From " + a_dbQName + ".sys.fulltext_indexes");
            ftBackgroundIndexes = (int)m_target.ExecuteScalar ("SELECT count(distinct t.name) FROM " + a_dbQName + ".sys.fulltext_indexes f join " + a_dbQName + ".sys.tables t on (f.object_id=t.object_id) WHERE f.change_tracking_state = 'A'");

            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.CommandText =
@"Insert c_ft (run_id,srv_id,inst_id,dbid,Catalogs,Tables,LastCrawl,BackgroundIndexes) 
Values (@run_id,@srv_id,@inst_id,@dbid,@Catalogs,@Tables,@LastCrawl,@BackgroundIndexes) ";
            sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
            sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
            sqlcmd.Parameters.AddWithValue ("@dbid", a_dbId);
            sqlcmd.Parameters.AddWithValue ("@Catalogs", ftCatalogs);
            sqlcmd.Parameters.AddWithValue ("@Tables", ftTables);
            sqlcmd.Parameters.AddWithValue ("@LastCrawl", ftLastCrawl);
            sqlcmd.Parameters.AddWithValue ("@BackgroundIndexes", ftBackgroundIndexes);

            UnitAddData (sqlcmd);
		}

		void CollectBackups (int a_dbId, string a_dbName, DateTime a_refTime)
		{
            // Unquoted db name!

			SqlDataReader dr;
			SqlCommand command;
			string bkType;
			int bkMin,
				bkMax,
				bkAvg,
				q;
			
			command = new SqlCommand (
@"Select type, count(*), 
min(DATEDIFF(ss,backup_start_date,backup_finish_date)), 
max(DATEDIFF(ss,backup_start_date,backup_finish_date)), 
avg(DATEDIFF(ss,backup_start_date,backup_finish_date)) 
from msdb.dbo.backupset where database_name='"+FixSingleQuote (a_dbName)+"' and backup_start_date > @refTime group by type ", 
				m_target.SQLConnection);
            command.Parameters.AddWithValue ("refTime", a_refTime);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					bkType = dr.GetString (0);
					q = dr.GetInt32 (1);
					bkMin = dr.GetInt32 (2);
					bkMax = dr.GetInt32 (3);
					bkAvg = dr.GetInt32 (4);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_backups (run_id,srv_id,inst_id,dbid,type,minTime,maxTime,avgTime,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@type,@minTime,@maxTime,@avgTime,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@type", bkType);
                    sqlcmd.Parameters.AddWithValue ("@minTime", bkMin);
                    sqlcmd.Parameters.AddWithValue ("@maxTime", bkMax);
                    sqlcmd.Parameters.AddWithValue ("@avgTime", bkAvg);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		void CollectTextInRow (int a_dbId, string a_dbQName)
		{
			SqlDataReader dr;
			SqlCommand command;
            short Limit;
            int q;
			
			command = new SqlCommand (
@"Select ObjectProperty (object_id, 'TableTextInRowLimit'), count(*) 
from "+a_dbQName+".sys.objects where type='u' group by ObjectProperty (object_id, 'TableTextInRowLimit') ",
				m_target.SQLConnection);
			command.CommandTimeout = 60;

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					Limit = (short)dr.GetInt32 (0);
					q = dr.GetInt32 (1);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_TableTextInRowLimit (run_id,srv_id,inst_id,dbid,Limit,qObjects) 
Values (@run_id,@srv_id,@inst_id,@dbid,@Limit,@qObjects) ";
                    AddInstanceDbParams (sqlcmd, a_dbId);
                    sqlcmd.Parameters.AddWithValue ("@Limit", Limit);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

					UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}
		}

		#endregion

		#region Server objects
		void CollectServerObjects ()
		{
			CollectGlobalTraceFlags ();

			if (m_target.Version.H2level == H2ProductLevel.Sql2005 || m_target.Version.H2level == H2ProductLevel.Sql2008) 
			{
				CollectLinkedServers ();
				CollectEndpoints ();
				CollectDbMail (); // must be called after c_database9

				try 
				{
					CollectBadPageHistory ();
				} 
				catch 
				{
					ChangeStatus ("exception in DMV block: msdb.dbo.suspect_page_table", StatusEventType.DEBUG);
				}

				try
				{
					CollectCursors ();
				} 
				catch 
				{
					ChangeStatus ("exception in DMV block: sys.dm_exec_cursors", StatusEventType.DEBUG);
				}

				try
				{
					CollectOptimizer ();
				} 
				catch 
				{
					ChangeStatus ("exception in DMV block: sys.dm_exec_query_optimizer_info", StatusEventType.DEBUG);
				}
			
				CollectCertificates ();
			}
		}

		void CollectGlobalTraceFlags ()
		{
			SqlDataReader dr;
			SqlCommand command;
			int flag,
				status;
			string s_flags = "";

			command = new SqlCommand ("DBCC TRACESTATUS(-1)", m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					flag = dr.GetInt16 (0);
					status = dr.GetInt16 (1);

					if (status == 1)
					{
						s_flags += flag+" ";
					}
				}

				if (s_flags != "")
				{
                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_globalTraceFlags (run_id,srv_id,inst_id,flags) 
Values (@run_id,@srv_id,@inst_id,@flags) ";
                    AddInstanceParams (sqlcmd);
                    sqlcmd.Parameters.AddWithValue ("@flags", s_flags);

                    UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}		
		}

		void CollectEndpoints ()
		{
			SqlDataReader dr;
			SqlCommand command;
			int q;
			byte protocol,
				type;
			long affinity = 0;    // removed from Sql2005

			command = new SqlCommand (
                "Select protocol, type, count(*) from master.sys.endpoints group by protocol, type ",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					protocol = dr.GetByte (0);
					type = dr.GetByte (1);
					q = dr.GetInt32 (2);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_endpoints (run_id,srv_id,inst_id,protocol,type,affinity,qObjects) 
Values (@run_id,@srv_id,@inst_id,@protocol,@type,@affinity,@qObjects) ";
                    AddInstanceParams (sqlcmd);
                    sqlcmd.Parameters.AddWithValue ("@protocol", protocol);
                    sqlcmd.Parameters.AddWithValue ("@type", type);
                    sqlcmd.Parameters.AddWithValue ("@affinity", affinity);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

                    UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}		
		}

		void CollectLinkedServers ()
		{
			SqlDataReader dr;
			SqlCommand command;
			string provider;
            int q;
            bool isLinked;

			command = new SqlCommand (
                "Select provider, is_linked, count(*) from master.sys.servers group by provider, is_linked ",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					provider = dr.GetString (0);
					isLinked = dr.GetBoolean (1);
					q = dr.GetInt32 (2);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_linked_servers (run_id,srv_id,inst_id,provider,isLinked,qObjects) 
Values (@run_id,@srv_id,@inst_id,@provider,@isLinked,@qObjects) ";
                    AddInstanceParams (sqlcmd);
                    sqlcmd.Parameters.AddWithValue ("@provider", provider);
                    sqlcmd.Parameters.AddWithValue ("@isLinked", isLinked);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

                    UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}		
		}

		void CollectBadPageHistory ()
		{
			SqlDataReader dr;
			SqlCommand command;
            short dbId;
			int q,
				qErr,
				type;

			command = new SqlCommand (
@"Select database_id, event_type, count(*), sum(error_count) 
from msdb.dbo.suspect_pages group by database_id, event_type ",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					dbId = (short)dr.GetInt32 (0);
					type = dr.GetInt32 (1);
					q = dr.GetInt32 (2);
					qErr = dr.GetInt32 (3);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_badpagehistory (run_id,srv_id,inst_id,dbid,event_type,entry_count,error_count) 
Values (@run_id,@srv_id,@inst_id,@dbid,@event_type,@entry_count,@error_count) ";
                    AddInstanceDbParams (sqlcmd, dbId);
                    sqlcmd.Parameters.AddWithValue ("@event_type", type);
                    sqlcmd.Parameters.AddWithValue ("@entry_count", q);
                    sqlcmd.Parameters.AddWithValue ("@error_count", qErr);

                    UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}		
		}

		#region DbMail
		void CollectDbMail ()
		{
			bool isProfile = false,
				isAccount = false;

			if (m_MailHostDbId > 0) 
			{
				isProfile = CollectMailProfile ();
				isAccount = CollectMailAccount ();
			}

            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.CommandText =
@"Insert c_imail (run_id, srv_id, inst_id, hostId, isProfile, isAccount) 
Values (@run_id,@srv_id,@inst_id,@hostId,@isProfile,@isAccount) ";
            AddInstanceParams (sqlcmd);
            sqlcmd.Parameters.AddWithValue ("@hostId", m_MailHostDbId);
            sqlcmd.Parameters.AddWithValue ("@isProfile", isProfile);
            sqlcmd.Parameters.AddWithValue ("@isAccount", isAccount);

			UnitAddData (sqlcmd);
		}

		void CollectMailHost (short a_dbId, string a_dbQName)
		{
			int retcode = 0;

			retcode = (int)m_target.ExecuteScalar (
                "SELECT count(*) FROM "+a_dbQName+".sys.services WHERE name ='iMailRequestorService'");

			if (retcode > 0)
			{
				m_MailHostDbId = a_dbId;
			}
		}

		bool CollectMailProfile ()
		{
			object o = m_target.ExecuteScalar ("Exec msdb.dbo.sysmail_help_profile_sp");
			return (o != null);
		}

		bool CollectMailAccount ()
		{
			object o = m_target.ExecuteScalar ("Exec msdb.dbo.sysmail_help_account_sp");
			return (o != null);
		}
		#endregion
		
        void CollectCursors ()
		{
			SqlDataReader dr;
			SqlCommand command;
			string properties;
			int q,
				minBuffer,
				maxBuffer,
				avgBuffer;
            bool isAsyncPopulation,
                isCloseOnCommit,
                isOpen;


            command = new SqlCommand (
@"Select properties, is_async_population, is_close_on_commit, is_open, 
min(fetch_buffer_size), max(fetch_buffer_size), avg(fetch_buffer_size), count(*) from sys.dm_exec_cursors (0) 
group by properties, is_async_population, is_close_on_commit, is_open",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					properties = dr.GetString (0);
					isAsyncPopulation = dr.GetBoolean (1);
					isCloseOnCommit = dr.GetBoolean (2);
					isOpen = dr.GetBoolean (3);
					minBuffer = dr.GetInt32 (4);
					maxBuffer = dr.GetInt32 (5);
					avgBuffer = dr.GetInt32 (6);
					q = dr.GetInt32 (7);

                    SqlCommand sqlcmd = new SqlCommand ();
                    sqlcmd.CommandText =
@"Insert dbo.c_cursors (run_id,srv_id,inst_id,properties,is_async_population,is_close_on_commit,is_open,minBuffer,maxBuffer,avgBuffer,qObjects) 
Values (@run_id,@srv_id,@inst_id,@properties,@is_async_population,@is_close_on_commit,@is_open,@minBuffer,@maxBuffer,@avgBuffer,@qObjects) ";
                    AddInstanceParams (sqlcmd);
                    sqlcmd.Parameters.AddWithValue ("@properties", properties);
                    sqlcmd.Parameters.AddWithValue ("@is_async_population", isAsyncPopulation);
                    sqlcmd.Parameters.AddWithValue ("@is_close_on_commit", isCloseOnCommit);
                    sqlcmd.Parameters.AddWithValue ("@is_open", isOpen);
                    sqlcmd.Parameters.AddWithValue ("@minBuffer", minBuffer);
                    sqlcmd.Parameters.AddWithValue ("@maxBuffer", maxBuffer);
                    sqlcmd.Parameters.AddWithValue ("@avgBuffer", avgBuffer);
                    sqlcmd.Parameters.AddWithValue ("@qObjects", q);

                    UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}		
		}

		void CollectCertificates ()
		{
			SqlDataReader dr;
			SqlCommand command;
			string s_cmd,
				pkType;
			int q;

			command = new SqlCommand (
"Select pvt_key_encryption_type, count(*) from sys.certificates group by pvt_key_encryption_type ",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					pkType = dr.GetString (0);
					q = dr.GetInt32 (1);

					s_cmd = "Insert dbo.c_certificates (run_id,srv_id,inst_id,pvt_key_encryption_type,qObjects) "
						+"Values ("
						+m_rd.run_id+", "
						+m_rd.srv_id+", "
						+m_rd.inst_id+", "
						+"'"+pkType+"', "
						+q+")"; 

					UnitAddData (s_cmd);
				}
			}
			finally
			{
				dr.Close ();
			}		
		}

		void CollectOptimizer ()
		{
			SqlDataReader dr;
			SqlCommand command;
			string counter;
			long occurrence;
			double value;

			command = new SqlCommand ("Select counter, occurrence, IsNULL(value,-1) from sys.dm_exec_query_optimizer_info",
				m_target.SQLConnection);

			dr = command.ExecuteReader ();
			try
			{
				while (dr.Read ())
				{
					counter = dr.GetString (0);
					occurrence = dr.GetInt64 (1);
					value = dr.GetDouble (2);

//spri_optimizer @run_id int, @srv_id int, @inst_id int, @counter nvarchar(1024), @occurrence bigint, @value float
                    SqlCommand sqlcmd = new SqlCommand ("dbo.spri_optimizer");
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    AddInstanceParams (sqlcmd);
                    sqlcmd.Parameters.AddWithValue ("@counter", counter);
                    sqlcmd.Parameters.AddWithValue ("@occurrence", occurrence);
                    sqlcmd.Parameters.AddWithValue ("@value", value);

                    UnitAddData (sqlcmd);
				}
			}
			finally
			{
				dr.Close ();
			}		
		}

		#endregion

		#region Other Collection functions
		void CollectTraceInfo () 
		{
			SqlCommand cmd;
			SqlDataAdapter da;
			DataTable dt;
			int trace_id,
				property,
				iValue;
			long lValue;
			string sValue;
			DateTime dtValue;

			cmd = new SqlCommand ("Select * from ::fn_trace_getinfo (default)", m_target.SQLConnection);
			da = new SqlDataAdapter (cmd);
			dt = new DataTable ();

			da.Fill (dt);

			foreach (DataRow dr in dt.Rows)
			{
				trace_id = (int)dr[0];
				property = (int)dr[1];
				sValue = "null";
				switch (property)
				{
					case 1:
					case 5:
						iValue = (int)dr[2];
						sValue = iValue.ToString ();
						break;
					case 3:
						if (dr[2] != System.DBNull.Value) 
						{
							lValue = (long)dr[2];
							sValue = lValue.ToString ();
						}
						break;
					case 2:
						if (dr[2] != System.DBNull.Value)
						{
							sValue = (string)dr[2];
						}
						break;
					case 4:
						if (dr[2] != System.DBNull.Value) 
						{
							dtValue = (DateTime)dr[2];
							sValue = dtValue.ToString ();
						}
						break;
				}

				StoreTrace (trace_id, property, sValue);
			}
		}

		void CollectRegistry ()
		{
			CollectRegistryWatson ();
			CollectRegistryErrorlog ();
		}

        void CollectRegistryWatson ()
        {
            string s_key = String.Empty;
            RegistryKey rk;
            int iWatson = 0,
                val;

            ChangeStatus (" Collecting Watson settings from Registry");
            if (m_target.Version.H2level == H2ProductLevel.Sql2000SP3)
            {
                s_key = m_InstanceKey + "\\Setup";
            }
            else if (m_target.Version.H2level == H2ProductLevel.Sql2005)
            {
                s_key = "SOFTWARE\\Microsoft\\Microsoft SQL Server\\90";
            }
            else if (m_target.Version.H2level == H2ProductLevel.Sql2008)
            {
                s_key = "SOFTWARE\\Microsoft\\Microsoft SQL Server\\100";
            }

            rk = m_hklm.OpenSubKey (s_key);

            try
            {
                iWatson = (int)rk.GetValue ("EnableErrorReporting");
            }
            catch (NullReferenceException) { }

            #region Additional Watson Info
            // Gathering additional info that should be implemented in Sql2005 
            // Per instance Watson setting
            if (m_target.Version.H2level == H2ProductLevel.Sql2005 || m_target.Version.H2level == H2ProductLevel.Sql2008)
            {
                try
                {
                    val = (int)rk.GetValue ("CustomerFeedback");
                    if (val == 1)
                    {
                        iWatson |= 0x10;
                    }
                }
                catch { }

                rk = m_hklm.OpenSubKey (m_InstanceKey + "\\CPE");
                try
                {
                    val = (int)rk.GetValue ("EnableErrorReporting");
                    if (val == 1)
                    {
                        iWatson |= 0x02;
                    }
                    val = (int)rk.GetValue ("CustomerFeedback");
                    if (val == 1)
                    {
                        iWatson |= 0x04;
                    }
                }
                catch { }
            }

            // CER
            try
            {
                rk = m_hklm.OpenSubKey ("Software\\Policies\\Microsoft\\PCHealth\\ErrorReporting\\DW");
                string s = (string)rk.GetValue ("DWFileTreeRoot");
                if (!String.IsNullOrEmpty (s))
                {
                    iWatson |= 0x08;
                }
            }
            catch { }

            #endregion Additional Watson Info

            StoreWatson (iWatson);
        }

		void CollectRegistryErrorlog ()
		{
			string errorlog_path = String.Empty,
				errorlog_fullpath,
				s_key;
			short nlogs = 6;
			long size = 0;
			RegistryKey lkey,
				nkey;
			DirectoryInfo dir;

			s_key = m_InstanceKey + "\\MSSQLServer\\Parameters";

			lkey = m_hklm.OpenSubKey (s_key);
			foreach (string val in lkey.GetValueNames ())
			{
				string p = (string)lkey.GetValue (val);
				if (p.StartsWith ("-e"))
				{
					errorlog_path = p.Substring (2);
					break;
				}
			}

			if (String.IsNullOrEmpty (errorlog_path))
			{
                LogMgrError (RunErrorClass.Registry, 0, s_key, CollectorError.CannotFindValue, 0, "Cannot find -e parameter");
                throw new CollectorErrorException (CollectorError.CannotFindValue, 0, "Cannot find errorlog path in the registry");
			}

			nkey = m_hklm.OpenSubKey (m_InstanceKey+"\\MSSQLServer");
			try
			{
				nlogs = (short)(int)nkey.GetValue ("NumErrorLogs");
			}
			catch (NullReferenceException) {}

			errorlog_fullpath = "\\\\"+m_machine_name+"\\"+errorlog_path.Replace (':','$');
			FileInfo errorlog = new FileInfo (errorlog_fullpath);
			try
			{
				size = errorlog.Length;
                SqlCommand sqlcmd = new SqlCommand ();
                sqlcmd.CommandText =
@"Insert dbo.c_errorlog (run_id,srv_id,inst_id,path,logsize,depth) 
Values (@run_id,@srv_id,@inst_id,@path,@logsize,@depth) ";
                AddInstanceParams (sqlcmd);
                sqlcmd.Parameters.AddWithValue ("@path", errorlog_path);
                sqlcmd.Parameters.AddWithValue ("@logsize", size);
                sqlcmd.Parameters.AddWithValue ("@depth", nlogs);

                UnitAddData (sqlcmd);
			}
			catch (FileNotFoundException fex)
			{
                LogMgrError (RunErrorClass.File, 0, errorlog_fullpath, CollectorError.FileCannotFindFile, 0, "Can't find file");
				ChangeStatus (fex.Message, StatusEventType.ERROR);
			}

			try
			{
				dir = errorlog.Directory;
				CollectDumps (dir);
			}
			catch (DirectoryNotFoundException dex)
			{
                LogMgrError (RunErrorClass.File, 0, errorlog_fullpath, CollectorError.FileCannotFindPath, 0, "Can't find path");
				ChangeStatus (dex.Message, StatusEventType.ERROR);
			}
		}

		void CollectDumps (DirectoryInfo a_dir)
		{
			DateTime cutoff,
				timeToCompare;

			ChangeStatus (" Collecting dumps info");

            SqlCommand command = new SqlCommand (
                "Select IsNULL(max(ftime),0) From dbo.c_dumps Where srv_id=@srv_id and inst_id=@inst_id",
                m_repository.SQLConnection);
            command.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            command.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);

			cutoff = (DateTime)command.ExecuteScalar ();

			foreach (FileInfo fi in a_dir.GetFiles ("SQLDump*.m*"))
			{
				timeToCompare = CollectorBase.MIN_DATE;
				CollectorBase.CopySimpleDate (fi.CreationTime, ref timeToCompare);
				if (timeToCompare <= cutoff) continue;
				StoreDump (fi);
			}
		}

		#endregion

		#region Store functions
		void StoreTrace (int a_trace_id, int a_property, string a_value)
		{
            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.CommandText = 
"Insert dbo.c_trace (run_id,srv_id,inst_id,trace_id,property,value) Values (@run_id,@srv_id,@inst_id,@trace_id,@property,@value) ";
            sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
            sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
            sqlcmd.Parameters.AddWithValue ("@trace_id", a_trace_id);
            sqlcmd.Parameters.AddWithValue ("@property", a_property);
            sqlcmd.Parameters.AddWithValue ("@value", a_value);

			UnitAddData (sqlcmd);
		}

		void StoreDump (FileInfo a_fi)
		{
            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.CommandText = 
"Insert dbo.c_dumps (run_id,srv_id,inst_id,fname,fsize,ftime) Values (@run_id,@srv_id,@inst_id,@fname,@fsize,@ftime) ";
            sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
            sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
            sqlcmd.Parameters.AddWithValue ("@fname", a_fi.Name);
            sqlcmd.Parameters.AddWithValue ("@fsize", a_fi.Length);
            sqlcmd.Parameters.AddWithValue ("@ftime", a_fi.CreationTime);

			UnitAddData (sqlcmd);
		}

		void StoreWatson (int a_value)
		{
            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.CommandText =
@"Insert dbo.c_watson (run_id, srv_id, inst_id, value) Values (@run_id, @srv_id, @inst_id, @value) ";
            AddInstanceParams (sqlcmd);
            sqlcmd.Parameters.AddWithValue ("@value", (short)a_value);

			UnitAddData (sqlcmd);
		}

		void StoreInstance ()
		{
            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.Connection = m_repository.SQLConnection;
            sqlcmd.CommandText =
@"Insert dbo.c_instance (run_id, srv_id, inst_id, major, minor, build, version, edition, plevel, IsRunning) 
Values (@run_id, @srv_id, @inst_id, @major, @minor, @build, @version, @edition, @plevel, @IsRunning) ";
            sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
            sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
            sqlcmd.Parameters.AddWithValue ("@major", m_instance.cn.Version.major);
            sqlcmd.Parameters.AddWithValue ("@minor", m_instance.cn.Version.minor);
            sqlcmd.Parameters.AddWithValue ("@build", m_instance.cn.Version.build);
            sqlcmd.Parameters.AddWithValue ("@version", m_instance.cn.Version.version);
            sqlcmd.Parameters.AddWithValue ("@edition", m_instance.cn.Version.edition);
            sqlcmd.Parameters.AddWithValue ("@plevel", m_instance.cn.Version.level);
            sqlcmd.Parameters.AddWithValue ("@IsRunning", m_instance.IsRunning);

            // Direct insert performed out of Unit context
            // It's atomic so it doesn't violate transactional integrity
            sqlcmd.ExecuteNonQuery ();
		}

		void StoreSyscurconfigs (int a_value, short a_config)
		{
            SqlCommand sqlcmd = new SqlCommand ();
            sqlcmd.CommandText =@"Insert dbo.c_syscurconfigs (run_id, srv_id, inst_id, value, config) 
Values (@run_id, @srv_id, @inst_id, @value, @config) ";
            AddInstanceParams (sqlcmd);
            sqlcmd.Parameters.AddWithValue ("@value", a_value);
            sqlcmd.Parameters.AddWithValue ("@config", a_config);

			UnitAddData (sqlcmd);
		}
		#endregion

		#region History functions 
		void HistoryDatabases ()
		{
			CallHistoryProc ("sph_database");
			if  ((m_target.Version.H2level == H2ProductLevel.Sql2005 || m_target.Version.H2level == H2ProductLevel.Sql2008)
                && (m_options & 0x100) > 0) 
			{
				CallHistoryProc ("sph_rs");
				CallHistoryProc ("sph_database2");
			}
		}

		void HistoryAllocations ()
		{
			if ((m_target.Version.H2level == H2ProductLevel.Sql2005 ||m_target.Version.H2level == H2ProductLevel.Sql2008)
                && (m_options & 0x100) > 0) 
			{
				CallHistoryProc ("sph_dataspace");
				CallHistoryProc ("sph_partition_scheme");
				CallHistoryProc ("sph_partition_function");
				CallHistoryProc ("sph_partition_parameters");
			}
			CallHistoryProc ("sph_filegroup");
			CallHistoryProc ("sph_dbfile");
		}

		void HistoryDbObjects ()
		{
			if ((m_target.Version.H2level == H2ProductLevel.Sql2005 || m_target.Version.H2level == H2ProductLevel.Sql2008)
                && (m_options & 0x100) > 0) 
			{
				CallHistoryProc ("sph_dbobject");
				CallHistoryProc ("sph_indexes");
				CallHistoryProc ("sph_triggers");
				CallHistoryProc ("sph_internal_tables");
				CallHistoryProc ("sph_assemblies");
				CallHistoryProc ("sph_assembly_types");
				CallHistoryProc ("sph_columns");
				CallHistoryProc ("sph_event_notifications");
				CallHistoryProc ("sph_notification_subscriptions");
				CallHistoryProc ("sph_ft");
				CallHistoryProc ("sph_sb");
				CallHistoryProc ("sph_backups");
				CallHistoryProc ("sph_TableTextInRowLimit");
			}
		}

		void HistoryServerObjects ()
		{
            if (m_target.Version.H2level == H2ProductLevel.Sql2005 || m_target.Version.H2level == H2ProductLevel.Sql2008) 
			{
				CallHistoryProc ("sph_linked_servers");
				CallHistoryProc ("sph_endpoints");
				CallHistoryProc ("sph_imail");
				//CallHistoryProc ("sph_badpagehistory");
				CallHistoryProc ("sph_cursors");
				CallHistoryProc ("sph_optimizer");
				CallHistoryProc ("sph_certificates");
			}
		}

        void HistoryJobs()
        {
            CallHistoryProc("sph_jobs");
            CallHistoryProc("sph_jobhistory");
        }
		#endregion

		#region Rosetta 
		void QueryRosettaWmi ()
		{
            if (m_target.ProductLevel == H2ProductLevel.Sql2005)
            {
                string scope = "\\\\" + m_machine_name + "\\root\\Microsoft\\SqlServer\\ReportingServices\\v9";
                string query = "SELECT * FROM MSReportServer_ConfigurationSetting";

                try
                {
                    CollectWmiNoMsg (scope, query, 1);
                }
                catch
                {
                }
            }
            else if (m_target.ProductLevel == H2ProductLevel.Sql2008)
            {
                ChangeStatus ("Querying Reporting Services WMI provider is not implemented for SQL Server 2008",
                     StatusEventType.WARNING);
            }
		}


		protected override void StoreWmi (uint a_type, ManagementObject a_mo)
		{
			string serverName = "",
				dbName;

			try
			{
				serverName = (string)a_mo["DatabaseServerName"];

				if (0 == String.Compare (serverName, m_instance.name, true))
				{
					try
					{
						dbName = (string)a_mo["DatabaseName"]; 
						m_RosettaDb_array.Add (dbName);
					}
					catch 
					{
						ChangeStatus ("exception in RosettaWMI dbName block", StatusEventType.DEBUG);
					}
				}
			}
			catch 
			{
				ChangeStatus ("exception in RosettaWMI serverName block", StatusEventType.DEBUG);
			}
		}

		void CollectRosetta (int a_dbId, string a_dbName, DateTime a_reftime)
		{
			int q;
			string cmd;

            SqlCommand command = new SqlCommand (
                "Select count(*) from " + a_dbName + ".dbo.ExecutionLog with (nolock) Where TimeStart > @refTime ",
                m_target.SQLConnection);
            command.Parameters.AddWithValue ("@refTime", a_reftime);

            q = (int)command.ExecuteScalar ();

			cmd = "Insert dbo.c_rs (run_id,srv_id,inst_id,dbid,qObjects) "
				+"Values ("+m_rd.run_id+", "+m_rd.srv_id+", "+m_rd.inst_id+", "+a_dbId+", "+q
				+")";

			UnitAddData (cmd);
		}

		#endregion

		#region SYSPERFINFO
		long getSumCounter (SqlCommand sqlcmd)
		{
			long v;

            object o = sqlcmd.ExecuteScalar ();
			if (o.GetType () == typeof (System.Int32))
			{
				v = (long)(int)o;
			}
			else
			{
				v = (long)o;
			}
			return v;
		}

		void c_sysperfinfo ()
		{
			long OpenConnectionCount,
				BrokerTransactionRollbacks,
				MessageSendsSec,
				MessageReceivesSec,
				MirroringTotalSends,
				MirroringTotalBytesSent,
				MirroringTransactionDelay,
				HttpAuthenticatedRequests,
				SoapSqlRequests,
				SoapWsdlRequests,
				SoapMethodInvocations,
				UserConnections,
				Transactions,
				SnapshotTransactions
				;

            SqlCommand sqlcmd = new SqlCommand (
                "Select IsNULL(sum(cntr_value),-1) from sys.dm_os_performance_counters where counter_name=@counter ",
                m_target.SQLConnection);
            SqlParameter param = sqlcmd.Parameters.AddWithValue ("@counter", "Open Connection Count");

			OpenConnectionCount = getSumCounter (sqlcmd);
			if (OpenConnectionCount == -1)
			{
                LogMgrError (RunErrorClass.Database, 0, "SysPerfInfo", CollectorError.SqlMissingPerfCounter, 0, "No performance counters found");
				ChangeStatus ("No performance counters found", StatusEventType.WARNING);
				return;
			}

            param.Value = "Broker Transaction Rollbacks";
			BrokerTransactionRollbacks = getSumCounter (sqlcmd);
            param.Value = "Message Fragment Sends/sec";
			MessageSendsSec = getSumCounter (sqlcmd);
            param.Value = "Message Fragment Receives/sec";
            MessageReceivesSec = getSumCounter (sqlcmd);
            param.Value = "Mirroring Total Sends";
			MirroringTotalSends = getSumCounter (sqlcmd);
            param.Value = "Mirroring Total Bytes Sent";
			MirroringTotalBytesSent = getSumCounter (sqlcmd);
            param.Value = "Mirroring Transaction Delay";
			MirroringTransactionDelay = getSumCounter (sqlcmd);
            param.Value = "HTTP Authenticated Requests";
			HttpAuthenticatedRequests = getSumCounter (sqlcmd);
            param.Value = "SOAP SQL Requests";
			SoapSqlRequests = getSumCounter (sqlcmd);
            param.Value = "SOAP WSDL Requests";
			SoapWsdlRequests = getSumCounter (sqlcmd);
            param.Value = "SOAP Method Invocations";
			SoapMethodInvocations = getSumCounter (sqlcmd);
            param.Value = "User Connections";
			UserConnections = getSumCounter (sqlcmd);
            param.Value = "Snapshot Transactions";
			SnapshotTransactions = getSumCounter (sqlcmd);

            sqlcmd = new SqlCommand (
@"Select IsNULL(sum(cntr_value),-1) from sys.dm_os_performance_counters where counter_name=@counter 
and RTRIM(object_name) like @object", m_target.SQLConnection);
            sqlcmd.Parameters.AddWithValue ("@counter", "Transactions");
            sqlcmd.Parameters.AddWithValue ("@object", "%Transactions");
            Transactions = getSumCounter (sqlcmd);

            sqlcmd = new SqlCommand ();
            sqlcmd.CommandText = 
@"Insert dbo.c_sysperf (run_id,srv_id,inst_id,OpenConnectionCount,BrokerTransactionRollbacks,MessageSendsSec,MessageReceivesSec,
MirroringTotalSends,MirroringTotalBytesSent,MirroringTransactionDelay,
HttpAuthenticatedRequests,SoapSqlRequests,SoapWsdlRequests,SoapMethodInvocations,
UserConnections,Transactions,SnapshotTransactions) 
Values (@run_id,@srv_id,@inst_id,@OpenConnectionCount,@BrokerTransactionRollbacks,@MessageSendsSec,@MessageReceivesSec,
@MirroringTotalSends,@MirroringTotalBytesSent,@MirroringTransactionDelay,
@HttpAuthenticatedRequests,@SoapSqlRequests,@SoapWsdlRequests,@SoapMethodInvocations,
@UserConnections,@Transactions,@SnapshotTransactions) ";
            sqlcmd.Parameters.AddWithValue ("@run_id", m_rd.run_id);
            sqlcmd.Parameters.AddWithValue ("@srv_id", m_rd.srv_id);
            sqlcmd.Parameters.AddWithValue ("@inst_id", m_rd.inst_id);
            sqlcmd.Parameters.AddWithValue ("@OpenConnectionCount", OpenConnectionCount);
            sqlcmd.Parameters.AddWithValue ("@BrokerTransactionRollbacks", BrokerTransactionRollbacks);
            sqlcmd.Parameters.AddWithValue ("@MessageSendsSec", MessageSendsSec);
            sqlcmd.Parameters.AddWithValue ("@MessageReceivesSec", MessageReceivesSec);
            sqlcmd.Parameters.AddWithValue ("@MirroringTotalSends", MirroringTotalSends);
            sqlcmd.Parameters.AddWithValue ("@MirroringTotalBytesSent", MirroringTotalBytesSent);
            sqlcmd.Parameters.AddWithValue ("@MirroringTransactionDelay", MirroringTransactionDelay);
            sqlcmd.Parameters.AddWithValue ("@HttpAuthenticatedRequests", HttpAuthenticatedRequests);
            sqlcmd.Parameters.AddWithValue ("@SoapSqlRequests", SoapSqlRequests);
            sqlcmd.Parameters.AddWithValue ("@SoapWsdlRequests", SoapWsdlRequests);
            sqlcmd.Parameters.AddWithValue ("@SoapMethodInvocations", SoapMethodInvocations);
            sqlcmd.Parameters.AddWithValue ("@UserConnections", UserConnections);
            sqlcmd.Parameters.AddWithValue ("@Transactions", Transactions);
            sqlcmd.Parameters.AddWithValue ("@SnapshotTransactions", SnapshotTransactions);

			UnitAddData (sqlcmd);
		}

		#endregion

        #region Collect Jobs

        void CollectSQLJobs()
        {
            
                ChangeStatus(" Extracting jobs for " + m_inst_name, StatusEventType.INFO);

                SqlDataReader drJobs;
                SqlCommand CmdGetJobs;
                #region "Sql Select String - GetJobSqlString"
                string GetJobSqlString = @"SELECT [job_id]
                                           ,[name]
                                           ,[enabled]
                                           ,[description]
                                           ,[start_step_id]
                                           ,[category_id]
                                           ,[owner_sid]
                                           ,[notify_level_eventlog]
                                           ,[notify_level_email]
                                           ,[notify_level_netsend]
                                           ,[notify_level_page]
                                           ,[notify_email_operator_id]
                                           ,[notify_netsend_operator_id]
                                           ,[notify_page_operator_id]
                                           ,[delete_level]
                                           ,[date_created]
                                           ,[date_modified]
                                           ,[version_number]
		                                FROM msdb..sysjobs";
                #endregion

                CmdGetJobs = new SqlCommand(GetJobSqlString, m_target.SQLConnection);
                CmdGetJobs.CommandType = CommandType.Text;
                drJobs = CmdGetJobs.ExecuteReader();

                SqlCommand CmdInsertJobs;
                #region "Sql Insert String - InsertJobsSqlString"
                string InsertJobsSqlString = @"INSERT INTO [dbo].[c_jobs]
                                               ([run_id]
                                               ,[srv_id]
                                               ,[inst_id]
                                               
                                               ,[job_id]
                                               ,[name]
                                               ,[enabled]
                                               ,[description]
                                               ,[start_step_id]
                                               ,[category_id]
                                               ,[owner_sid]
                                               ,[notify_level_eventlog]
                                               ,[notify_level_email]
                                               ,[notify_level_netsend]
                                               ,[notify_level_page]
                                               ,[notify_email_operator_id]
                                               ,[notify_netsend_operator_id]
                                               ,[notify_page_operator_id]
                                               ,[delete_level]
                                               ,[date_created]
                                               ,[date_modified]
                                               ,[version_number])
                                         VALUES (
			                                    @run_id
                                               ,@srv_id
                                               ,@inst_id
                                               
                                               ,@job_id
                                               ,@name
                                               ,@enabled
                                               ,@description
                                               ,@start_step_id
                                               ,@category_id
                                               ,@owner_sid
                                               ,@notify_level_eventlog
                                               ,@notify_level_email
                                               ,@notify_level_netsend
                                               ,@notify_level_page
                                               ,@notify_email_operator_id
                                               ,@notify_netsend_operator_id
                                               ,@notify_page_operator_id
                                               ,@delete_level
                                               ,@date_created
                                               ,@date_modified
                                               ,@version_number )";
                #endregion

                try
                {

                    while (drJobs.Read())
                    {

                        CmdInsertJobs = new SqlCommand();

                        CmdInsertJobs.CommandText = InsertJobsSqlString;

                        CmdInsertJobs.Parameters.AddWithValue("@run_id", m_rd.run_id);
                        CmdInsertJobs.Parameters.AddWithValue("@srv_id", m_rd.srv_id);
                        CmdInsertJobs.Parameters.AddWithValue("@inst_id", m_rd.inst_id);

                        CmdInsertJobs.Parameters.AddWithValue("@job_id", drJobs["job_id"]);
                        CmdInsertJobs.Parameters.AddWithValue("@name", drJobs["name"]);
                        CmdInsertJobs.Parameters.AddWithValue("@enabled", drJobs["enabled"]);
                        CmdInsertJobs.Parameters.AddWithValue("@description", drJobs["description"]);
                        CmdInsertJobs.Parameters.AddWithValue("@start_step_id", drJobs["start_step_id"]);
                        CmdInsertJobs.Parameters.AddWithValue("@category_id", drJobs["category_id"]);
                        CmdInsertJobs.Parameters.AddWithValue("@owner_sid", drJobs["owner_sid"]);
                        CmdInsertJobs.Parameters.AddWithValue("@notify_level_eventlog", drJobs["notify_level_eventlog"]);
                        CmdInsertJobs.Parameters.AddWithValue("@notify_level_email", drJobs["notify_level_email"]);
                        CmdInsertJobs.Parameters.AddWithValue("@notify_level_netsend", drJobs["notify_level_netsend"]);
                        CmdInsertJobs.Parameters.AddWithValue("@notify_level_page", drJobs["notify_level_page"]);
                        CmdInsertJobs.Parameters.AddWithValue("@notify_email_operator_id", drJobs["notify_email_operator_id"]);
                        CmdInsertJobs.Parameters.AddWithValue("@notify_netsend_operator_id", drJobs["notify_netsend_operator_id"]);
                        CmdInsertJobs.Parameters.AddWithValue("@notify_page_operator_id", drJobs["notify_page_operator_id"]);
                        CmdInsertJobs.Parameters.AddWithValue("@delete_level", drJobs["delete_level"]);
                        CmdInsertJobs.Parameters.AddWithValue("@date_created", drJobs["date_created"]);
                        CmdInsertJobs.Parameters.AddWithValue("@date_modified", drJobs["date_modified"]);
                        CmdInsertJobs.Parameters.AddWithValue("@version_number", drJobs["version_number"]);

                        UnitAddData(CmdInsertJobs);
                    }
                }
                finally{
                    drJobs.Close();
                }
        }
        

        void CollectSqlJobHistory() {

                ChangeStatus(" Extracting job history for " + m_inst_name, StatusEventType.INFO);

                SqlDataReader drJobHistory;
                SqlCommand CmdGetJobHistory;
                #region "Sql Select String - GetJobSqlString"
                string GetJobSqlString = @"SELECT [instance_id]
                                              ,[job_id]
                                              ,[step_id]
                                              ,[step_name]
                                              ,[sql_message_id]
                                              ,[sql_severity]
                                              ,[message]
                                              ,[run_status]
                                              ,run_date
	                                          ,run_time
                                              ,[run_duration]
                                              ,[operator_id_emailed]
                                              ,[operator_id_netsent]
                                              ,[operator_id_paged]
                                              ,[retries_attempted]
                                              ,[server]
                                        FROM [msdb].[dbo].[sysjobhistory]
                                        WHERE CONVERT(DATETIME, 
		                                        LEFT(CAST(run_date AS VARCHAR), 4) + '-' +
		                                        SUBSTRING(CAST(run_date AS VARCHAR), 5, 2) + '-' +
		                                        RIGHT(CAST(run_date AS VARCHAR), 2) + ' ' +
		                                        LEFT(CAST((REPLICATE('0', 6 - LEN([run_time])) + CAST([run_time] AS VARCHAR)) AS VARCHAR), 2) + ':' +
		                                        SUBSTRING(CAST((REPLICATE('0', 6 - LEN([run_time])) + CAST([run_time] AS VARCHAR)) AS VARCHAR), 3, 2) + ':' +
		                                        RIGHT(CAST((REPLICATE('0', 6 - LEN([run_time])) + CAST([run_time] AS VARCHAR)) AS VARCHAR), 2)
		                                        , 120) > DATEADD(wk, -1, getdate())";
                #endregion

                CmdGetJobHistory = new SqlCommand(GetJobSqlString, m_target.SQLConnection);
                CmdGetJobHistory.CommandType = CommandType.Text;
                drJobHistory = CmdGetJobHistory.ExecuteReader();

                SqlCommand CmdInsertJobHistory;
                #region "Sql Insert String - InsertJobHistorySqlString"
                string InsertJobHistorySqlString = @"INSERT INTO [dbo].[c_jobhistory]
                                           ([run_id]
                                           ,[srv_id]
                                           ,[inst_id]
                                           ,[instance_id]
                                           ,[job_id]
                                           ,[step_id]
                                           ,[step_name]
                                           ,[sql_message_id]
                                           ,[sql_severity]
                                           ,[message]
                                           ,[run_status]
                                           ,[run_date]
                                           ,[run_time]
                                           ,[run_duration]
                                           ,[operator_id_emailed]
                                           ,[operator_id_netsent]
                                           ,[operator_id_paged]
                                           ,[retries_attempted]
                                           ,[server])
                                     VALUES
                                           (@run_id
                                           ,@srv_id
                                           ,@inst_id
                                           ,@instance_id
                                           ,@job_id
                                           ,@step_id
                                           ,@step_name
                                           ,@sql_message_id
                                           ,@sql_severity
                                           ,@message
                                           ,@run_status
                                           ,@run_date
                                           ,@run_time
                                           ,@run_duration
                                           ,@operator_id_emailed
                                           ,@operator_id_netsent
                                           ,@operator_id_paged
                                           ,@retries_attempted
                                           ,@server)";
                #endregion

                try
                {
                    while (drJobHistory.Read())
                    {
                        CmdInsertJobHistory = new SqlCommand();

                        CmdInsertJobHistory.CommandText = InsertJobHistorySqlString;

                        CmdInsertJobHistory.Parameters.AddWithValue("@run_id", m_rd.run_id);
                        CmdInsertJobHistory.Parameters.AddWithValue("@srv_id", m_rd.srv_id);
                        CmdInsertJobHistory.Parameters.AddWithValue("@inst_id", m_rd.inst_id);

                        CmdInsertJobHistory.Parameters.AddWithValue("@instance_id", drJobHistory["instance_id"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@job_id", drJobHistory["job_id"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@step_id", drJobHistory["step_id"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@step_name", drJobHistory["step_name"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@sql_message_id", drJobHistory["sql_message_id"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@sql_severity", drJobHistory["sql_severity"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@message", drJobHistory["message"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@run_status", drJobHistory["run_status"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@run_date", drJobHistory["run_date"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@run_time", drJobHistory["run_time"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@run_duration", drJobHistory["run_duration"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@operator_id_emailed", drJobHistory["operator_id_emailed"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@operator_id_netsent", drJobHistory["operator_id_netsent"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@operator_id_paged", drJobHistory["operator_id_paged"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@retries_attempted", drJobHistory["retries_attempted"]);
                        CmdInsertJobHistory.Parameters.AddWithValue("@server", drJobHistory["server"]);

                        UnitAddData(CmdInsertJobHistory);
                    }
                }
                finally
                {
                    drJobHistory.Close();
                }
        }
        

        #endregion

    }
}

